在 SQL Server 中将表行展平为列

2022-01-22 00:00:00 pivot tsql sql-server-2008 sql-server sql-server-2014

我有下面的 SQL 表，其中有随机生成的数据

I have the below SQL table which has the data generated randomly

  Code          Data
    SL Payroll    22
    SL Payroll    33
    SL Payroll    43
    ..            .....

我要传输数据，格式如下图

I want to transfer the data so the format becomes as shown below

Code         Data1   Data2   Data3  ..
SL Payroll   22       33      43    ....

有人建议使用数据透视表来转换数据，如下所示

Someone suggested Pivot table to transform the data as below

SELECT Code,
       [22] Data1,
       [33] Data2,
       [43] Data3
FROM
    (
      SELECT *
      FROM T
    ) TBL
    PIVOT
    (
      MAX(Data) FOR Data IN([22],[33],[43])
    ) PVT

但这假设数据点是静态的，例如 22,33，但它们是动态生成的.

but this assumes the data points are static like 22,33 but they are dynamically generated.

推荐答案

我会使用条件聚合和 row_number():

I would use conditional aggregate along with row_number():

select code,
       max(case when seqnum = 1 then code end) as code_1,
       max(case when seqnum = 2 then code end) as code_2,
       max(case when seqnum = 3 then code end) as code_3
from (select t.*,
             row_number() over (partition by code order by data) as seqnum
      from t
     ) t
group by code;

相关文章