SQL Pivot String Data

Question

I have a tables in SQL Server: Product

Product Table:

ImageID  ProductID  
-------  ---------- 
1           P1
1           P2             
1           P3             
2           S1
2           S2
2           S3
3           M1

This is the output that I need:

ImageID  Product1ID     Product2ID      Product3ID
----------- ---------- ----------    ----------
1           P1             P2           P3
2           S1             S2           S3
3           M1             null         null

An ImageID can have maximum 3 ProductID It is not necessary that all ImageID will have 3 products [eg. ImageID=3]

SELECT ImageID, [Product1ID], [Product2ID], [Product3ID]
FROM    
(  
        SELECT ImageID,  ProductID
        FROM ProductTable
) AS P
PIVOT 
(  
    max( ImageID) 
    FOR ProductID IN ([Product1ID], [Product2ID], [Product3ID])
) AS  PVT

Gordon Linoff · Accepted Answer · 2018-12-13 15:00:44Z

4

I would just use conditional aggregation:

SELECT ImageID,
       MAX(CASE WHEN seqnum = 1 THEN ProductID END) as Product1ID,
       MAX(CASE WHEN seqnum = 2 THEN ProductID END) as Product2ID,
       MAX(CASE WHEN seqnum = 3 THEN ProductID END) as Product3ID
FROM (SELET pt.*, ROW_NUMBER() OVER (PARTITION BY ImageId ORDER BY ProductID) as seqnum
      FROM ProductTable
     ) P
GROUP BY ImageID;

The key idea, though, is to use ROW_NUMBER() to enumerate the products.

answered Dec 13, 2018 at 15:00

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dale K · Accepted Answer · 2020-06-04 05:33:36Z

3

Your were very close, you just needed to incorporate Row_Number()

Example

Select *
 From  (
        Select ImageID
              ,Item = concat('Product',row_number() over (partition by ImageID order by ProductID),'ID') 
              ,ProductID
         From ProductTable
       ) src
Pivot (max(ProductID) for Item in ([Product1ID], [Product2ID], [Product3ID])) pvt

edited Jun 4, 2020 at 5:33

Dale K

28.1k15 gold badges59 silver badges86 bronze badges

answered Dec 13, 2018 at 15:00

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Collectives™ on Stack Overflow

SQL Pivot String Data

2 Answers 2

Comments

Comments

Your Answer

Collectives™ on Stack Overflow

SQL Pivot String Data

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related