将多行转换为多列

Question

这是关于SQL Server的内容

ID       ID1      Days     Rank
96972    1        2976     1
96972    174      2976     2
96972    173      2966     3
96972    175      2966     4
96972    176      2963     5
96972    177      2961     6
96972    178      2960     7
96972    179      2952     8
96972    180      2945     9
96972    181      135      10

我在结果集中创建了下面的Rank列。

row_number() OVER(Partition by id ORDER BY somedate)  AS TimeLineOrders

我希望将这些结果显示在单行中，如下：

Timeline1     Timeline2     TimeLine3     Timeline4    Timeline 10
96972         ID1+Days      ID1+Days      ID1+Days     ID1+Days     ID1+Days

请问有谁能帮助我吗？

- user1744002

---

这是使用SQL Server ID ID1 Days Rank的内容：96972 1 2976 1 96972 174 2976 2 96972 173 2966 3 96972 175 2966 4 96972 176 2963 5 96972 177 2961 6 96972 178 2960 7 96972 179 2952 8 96972 180 2945 9 96972 181 135 10我在结果集中使用了Rank列，如下所示：row_number() OVER(Partition by id ORDER BY somedate) AS TimeLineOrders我想将这些结果显示在单行中，如下所示：Timeline1 Timeline2 TimeLine3 Timeline4 ...Timeline 10 96972 ID1+Days ID1+Days ID1+Days ID1+Days ID1+Days请问有人可以帮我吗？ - user1744002

为什么你在自己的问题下评论并复制问题内容？评论旁边有一个删除按钮。 - Tim Schmelter

Answer 1

由于缺乏表结构和最终结果，很难确切地知道您正在寻找什么。看起来你需要使用PIVOT来处理数据。你可以使用静态版本或动态版本进行PIVOT。

创建带有数据示例的表：

create table tableA
(
    id int,
    id1 int,
    days int
);

insert into tableA values
(96972,   1,   2976),
(96972,   174, 2976),
(96972,   173, 2966),
(96972,   175, 2966),
(96972,   176, 2963),
(96972,   177, 2961),
(96972,   178, 2960),
(96972,   179, 2952),
(96972,   180, 2945),
(96972,   181, 135);

静态版本，将值硬编码为枢轴:

select *
from 
(
    select id, 
        'Timeline_' + cast(ROW_NUMBER() over(PARTITION by id order by days desc) as varchar(10)) col,
        id1 + days value
    from tableA
) x
pivot
(
    max(value)
    for col in ([Timeline_1], [Timeline_2], [Timeline_3], [Timeline_4], 
                [Timeline_5], [Timeline_6], [Timeline_7], [Timeline_8], 
                [Timeline_9], [Timeline_10])
) p

动态版本，列在运行时生成：

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME( 'Timeline_' + cast(x.rn as varchar(10))) 
                    from tableA
                    cross apply 
                    (
                        select ROW_NUMBER() over(PARTITION by id order by days desc) rn
                        from tableA
                    ) x
                    group by x.rn
                    order by x.rn
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT id, ' + @cols + ' from 
             (
                select id, 
                    ''Timeline_'' + cast(ROW_NUMBER() over(PARTITION by id order by days desc) as varchar(10)) col,
                    id1 + days value
                from tableA
            ) x
            pivot 
            (
                max(value)
                for col in (' + @cols + ')
            ) p '

execute(@query)

- Taryn