使用 UNPIVOT 将行转换为列

2021-09-14 00:00:00 sql sql-server 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    CDE02  
1    XYZ03

我尝试使用 UNPIVOT，但我的代码不起作用:

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

有人可以帮我将这些行转换为每个 ID 的列吗?

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

推荐答案

或者，您可以使用 VALUES 子句来取消数据透视:

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<>小提琴

相关文章