SQL复杂动态Pivoting [英] SQL complex dynamic Pivoting

问题描述

我正在 SQL Server 中尝试对下表进行透视

Hi I am trying in SQL Server the pivoting for the following table

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

原表的列数已知但行数未知.谁能帮忙

The number of columns in the original table are known but the number of rows are not known. Can any one help

推荐答案

为了得到最终结果，你可以使用 PIVOT 函数，但首先我会取消你的 col1 和 col2 列，这样您就不会有多个要透视的列.

In order to get the final result, you can use the PIVOT function but first I would unpivot your col1 and col2 columns so you won't have multiple columns to pivot.

如果您的列数有限，那么您可以对查询进行硬编码:

If you have a limited number of columns, then you can hard-code the query:

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:

But it you have an unknown number of sequence values, then you can use dynamic 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;

这篇关于SQL复杂动态Pivoting的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！