SQL复杂动态数据透视

一尘不染

SQL复杂动态数据透视

sql

嗨，我正在尝试在SQL Server下表中进行数据透视

REFID | COL1 | COL2 | Sequence
1       abc    cde     1
1       lmn    rst     2
1       kna    asg     3
2       als    zkd     2
2       zpk    lad     1

我希望输出为

   COLNAME REFID | 1 | 2 | 3
     COL1   1     abc lmn kna
     COL2   1     cde rst asg
     COL1   2     zpk als null
     COL2   2     lad zkd null

原始表中的列数是已知的，但行数是未知的。谁能帮忙

阅读 191

2021-05-16

共1个答案

一尘不染

为了获得最终结果，您可以使用PIVOT函数，但首先我将取消透视您的col1和col2列，因此您将没有多个透视图。

如果列数有限，则可以对查询进行硬编码：

select REFID, col, [1], [2], [3]
from 
(
    select REFID, Sequence, col, value
    from yourtable
    cross apply
    (
        select 'COL1', col1 union all
        select 'COL2', col2
    ) c (col, value)
) d
pivot
(
    max(value)
    for sequence in ([1], [2], [3])
) piv
order by refid;

但是它具有未知数量的sequence值，那么您可以使用动态SQL：

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(sequence) 
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT refid, col,' + @cols + ' 
            from 
            (
                select REFID, Sequence, col, value
                from yourtable
                cross apply
                (
                    select ''COL1'', col1 union all
                    select ''COL2'', col2
                ) c (col, value)
            ) x
            pivot 
            (
                max(value)
                for Sequence in (' + @cols + ')
            ) p 
            order by refid'

execute sp_executesql @query;

2021-05-16