Sql-server – Grouping by more than 1 column using Partion By or any other method – Sql Server

group bysql servert-sql

I have the following tables in my SQL Server database:

Product
-Id
-Name
-Price
-TypeId (Foreign Key)
-SubTypeId (Foreign Key)

Type
-Id
-Name

SubType
-Id
-Name
-TypeId (Foreign Key)

I currently have a screen which displays products grouped by Type and SubType like this:

+------------+----------+-----------+
|    Type    | Sub Type |  Product  |
+------------+----------+-----------+
| Electrical | TV       | TV1       |
|            | TV       | TV2       |
|            | TV       | TV3       |
|            | TV       | TV4       |
|            | Hoovers  | Hoover1   |
|            | Hoovers  | Hoover2   |
| Furniture  | Bedroom  | Wardrobe1 |
|            | Bedroom  | Wardrobe2 |
|            | Dining   | Table1    |
|            | Dining   | Table2    |
+------------+----------+-----------+

Using windowing function i have managed to display the Type only once, my query:

    SELECT CASE WHEN RowNum = 1 THEN TypeName ELSE NULL END AS TypeName, SubTypeName, ProductName
    FROM (
    SELECT Type.Name AS TypeName, SubType.Name AS SubTypeName, Product.Name AS ProductName, ROW_NUMBER() OVER(PARTITION BY Type.Name ORDER BY Product.Name) AS Rownum
    FROM Product 
        INNER JOIN Type ON Type.Id = Product.TypeId
        INNER JOIN SubType ON SubType.Id = Product.SubTypeId
    GROUP BY Type.Name, SubType.Name Product.Name
    ) X

What i want to do is have something similar for the sub type column so sub types are only displayed once like this:

+------------+----------+-----------+
|    Type    | Sub Type |  Product  |
+------------+----------+-----------+
| Electrical | TV       | TV1       |
|            |          | TV2       |
|            |          | TV3       |
|            |          | TV4       |
|            | Hoovers  | Hoover1   |
|            |          | Hoover2   |
| Furniture  | Bedroom  | Wardrobe1 |
|            |          | Wardrobe2 |
|            | Dining   | Table1    |
|            |          | Table2    |
+------------+----------+-----------+

How can i adjust or change my query to make this work?

Best Answer

Just create 2 separate row numbers for change of Type and SubType. I used Id field since it looks like you don't want it to be sorted by Name:

Select
  case row_number() over (partition by T.Id order by T.Id, S.Id, P.Id)
    when 1 then T.Name else '' end as Type,
  case row_number() over (partition by T.Id, S.Id order by T.Id, S.Id, P.Id)
    when 1 then S.Name else '' end as SubType,
  P.Name
FROM 
  Product P
  INNER JOIN Type T ON T.Id = P.TypeId
  INNER JOIN SubType S ON S.Id = P.SubTypeId
 order by T.Id, S.Id, P.Id

Example in SQL Fiddle

Related Question