1

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?

0

3 Answers 3

4

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

0
2
SELECT CASE WHEN rn = 1 THEN TypeName ELSE NULL END AS TypeName 
     , ProductName, rn
FROM ( SELECT Type.Name AS [TypeName], Product.Name AS [ProductName]
            , ROW_NUMBER() OVER(PARTITION BY Type.Name ORDER BY Product.Name) AS rn    
         FROM Product 
         JOIN Type 
               ON Type.Id = Product.TypeId
     ) tt 
order by ProductName, rn 

ROW_NUMBER (Transact-SQL)

3
  • 2
    It's probably a good idea to elaborate in your answer on what it does and how it differs from other posted answers, for the sake of future readers who are not as comfortable with window functions in T-SQL. Code-only answers are generally hard to understand for many readers. Apr 16, 2016 at 5:49
  • @DanielHutmacher On inspection this does not have group by compared to the other
    – paparazzo
    Apr 16, 2016 at 9:32
  • @Paparazzi that's why your answer does not have down votes ;) It would be better if you also describes how it (query and window functions) work. Apr 16, 2016 at 9:44
0

Use this ones :

  Create Table [Type]
  (
    Id Int ,Name varchar(10)
  )
  Create Table [Product]
  (
    Id Int ,
    Name varchar(10),
    TypeID Int,
  )

  INSERT INTO [Type](ID,Name) 
    VALUES 
      (1,'Bikes'),
      (2,'Clothes'),
      (3,'Electrical')

  INSERT INTO [Product](ID,Name,TypeID) 
    VALUES 
      (1,'Product 1',1),
      (2,'Product 2',1),
      (3,'Product 3',1),
      (4,'Tshirt 1',2),
      (5,'Trousers 2',2),
      (6,'Hat 3',2),
      (7,'Shoes 4',2),
      (8,'Tv',3),
      (9,'Radio',3),
      (10,'Laptop',3)


  SELECT 
    CASE ProductWithRank.[Rank]
      WHEN 1 
        THEN [Type].Name 
      ELSE NULL 
    END AS TypeName 
    , ProductWithRank.Name, 
    ProductWithRank.[Rank]
  FROM ( SELECT                
            Product.TypeId, 
            Product.Name ,
            Product.ID
            , Rank() OVER(PARTITION BY Product.TypeId ORDER BY Product.ID) AS [Rank]    
         FROM Product 
       ) ProductWithRank 
  INNER JOIN [Type] ON [Type].Id = ProductWithRank.TypeId
  ORDER BY ProductWithRank.ID, [Rank] 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.