Converting Rows To Columns Using Unpivot

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 CDE

Solution 1:

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);

DB<>Fiddle