SQL复杂动态透视2

2022-01-22 00:00:00 pivot sql sql-server

您好，我正在 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

我希望输出为

        REFID | 1COL1 | 2COL1 | 3COL1 |1COL2|2COL2|3COL2
           1    abc     lmn      kna    cde   rst   asg
           2    zpk     als      null   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

推荐答案

如果您想将 sequence 编号作为列名的一部分，那么您仍然需要取消转置您的 col1 和 col2 列首先，然后应用枢轴.不同之处在于您会将 sequence 编号连接到在反透视过程中创建的列名.

If you want to include the sequence number as part of your column names, then you will still need to unpivot your col1 and col2 columns first, then apply the pivot. The difference is that you will concatenate the sequence number to your column names created during the unpivot process.

对于已知数量的值，查询将是:

For a known number of values the query would be:

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

如果你有一个未知的数字，动态 SQL 版本将是:

Then if you have an unknown number the dynamic SQL version will be:

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

select @cols = STUFF((SELECT ',' + QUOTENAME(cast(Sequence as varchar(10))+ col) 
                    from yourtable
                    cross apply
                    (
                        select 'Col1', 1 union all
                        select 'Col2', 2
                    ) c(col, so)
                    group by Sequence, col, so
                    order by  so, sequence
                    FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

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

execute sp_executesql @query;

相关文章