在 SQL Server 中将表行展平为列 [英] Flatten table rows into columns in SQL Server

问题描述

我有下面的 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;

这篇关于在 SQL Server 中将表行展平为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！