CODEWITHSUNDEEP
sqlsql-servert-sql
WhatsInAName
WhatsInAName

Reputation: 724

Multiple rows in multiple columns in a single row

Found different solutions titled this on SE and Google but not exactly what I am looking for.

I have two tables "Product":

ID    Name
1     Product1
2     Product2

and "Product_Group":

ID   ProductID    Name
1    1            Group1
2    1            Group2
3    1            Group3
4    2            Group4
5    2            Group5
6    2            Group6

I want to display the results like the following:

ProductName    G1        G2       G3
Product1       Group1    Group2   Group3
Product2       Group4    Group5   Group6

How can I do this?

Upvotes: 0

Views: 53

Answers (1)

gotqn
gotqn

Reputation: 43626

This is full working exmaple:

DECLARE @Products TABLE
(
     [ID] TINYINT
    ,[Name] VARCHAR(12)
)

DECLARE @ProductsGroups TABLE
(
     [ID] TINYINT
    ,[ProductID] TINYINT
    ,[Name] VARCHAR(12)
)

INSERT INTO @Products ([ID], [Name])
VALUES (1, 'Product1')
      ,(2, 'Product2')

INSERT INTO @ProductsGroups ([ID], [ProductID], [Name])
VALUES   (1,1,'Group1')
        ,(2,1,'Group2')
        ,(3,1,'Group3')
        ,(4,2,'Group4')
        ,(5,2,'Group5')
        ,(6,2,'Group6')

SELECT [Name]   
      ,[G1]
      ,[G2]
      ,[G3]
FROM
(
    SELECT P.[Name] AS [Name]
          ,PG.[Name] AS [Value]
          ,'G' + CAST(ROW_NUMBER() OVER (PARTITION BY P.[Name] ORDER BY PG.[Name]) AS VARCHAR(4)) AS [Group]
    FROM @Products P
    INNER JOIN @ProductsGroups PG
        ON P.[ID] = PG.[ProductID]
) DataSource
PIVOT
(
    MAX([Value]) FOR [Group] IN ([G1], [G2], [G3])
) PVT

Note, if you can have more G* groups you need to check solutions for dynamic pivots.

Upvotes: 1

Related Questions