SQL Server T-SQL – How to Remove Duplicates Using Group By

group bysql servert-sql

I have 2 tables, Product and Type, the tables looks something like this:

Product
-Id
-Name
-TypeId

Type
-Id
-Name

I want to be able to write a SQL Server query which outputs the products grouped by Type:

+------------+------------+
| Bikes      | Product 1  |
|            | Product 2  |
|            | Product 3  |
| Clothes    | Tshirt 1   |
|            | Trousers 2 |
|            | Hat 3      |
|            | Shoes 4    |
| Electrical | Tv         |
|            | Radio      |
|            | Laptop     |
+------------+------------+

If i write:

SELECT Type.Name,Product.Name
FROM Product INNER JOIN Type ON Type.Id = Product.TypeId
ORDER By Type.Name

I get similar results however the type value is included in every column.

+------------+------------+
| Bikes      | Product 1  |
| Bikes      | Product 2  |
| Bikes      | Product 3  |
| Clothes    | Tshirt 1   |
| Clothes    | Trousers 2 |
| Clothes    | Hat 3      |
| Clothes    | Shoes 4    |
| Electrical | Tv         |
| Electrical | Radio      |
| Electrical | Laptop     |
+------------+------------+

Using SQL Server is there any way i can only show the first type value?

Best Answer

Windowing functions can help here.

SELECT CASE WHEN RowNum = 1 THEN TypeName ELSE NULL END AS TypeName, ProductName
FROM (
SELECT Type.Name AS TypeName,Product.Name AS ProductName, ROW_NUMBER() OVER(PARTITION BY Type.Name ORDER BY Product.Name) AS Rownum, COUNT([whatever you want to count]) AS ItemCount
FROM Product INNER JOIN Type ON Type.Id = Product.TypeId
GROUP BY Type.Name, Product.Name
) X

The MSDN reference for the OVER clause is here, by the way:

https://msdn.microsoft.com/en-us/library/ms189461.aspx

Related Question