Converting rows to columns using UNPIVOT

Question

Following is my sample dataset:

ID  Prod1   Prod2   Prod3
1   ABC01   CDE02   XYZ03

I want to convert rows to columns and my desired output is:

ID   Products
1    ABC01  
1    CDE02  
1    XYZ03

I tried using UNPIVOT, but my code didn't work:

Select Id, Products
From Sample
UNPIVOT
(
    Products
    FOR Id IN ([1],[2],[3])
) AS P

Can someone please help me converting these rows to columns for each ID?

Thom A · Accepted Answer · 2019-07-26T14:39:26.673

4

Alternatively, you can use a VALUES clause to unpivot the data:

SELECT S.ID,
       V.Product
FROM dbo.Sample S
     CROSS APPLY (VALUES(S.Prod1),(S.Prod2),(S.Prod3))V(Product);

edited Jul 26 '19 at 14:39

answered Jul 26 '19 at 14:36

Thom A

OP is actually looking for PIVOT. The question is phrased backwards – John Cappelletti Jul 26 '19 at 14:37
That's not what their question says, @JohnCappelletti *"Following is my sample dataset: {de-normalised data} I want to convert rows to columns and my desired output is: {Normalised data}"* – Thom A Jul 26 '19 at 14:38
Yup I know. His comment to me was is data set was ID and Product and he wants to "flip them" – John Cappelletti Jul 26 '19 at 14:39
Undelete your answer then @JohnCappelletti , as it's clearly correct too. I *think* the OP may have been commenting on that the datatet returned the column "item" too. At a guess. – Thom A Jul 26 '19 at 14:40

1 Answers1