SQL复杂动态数据透视2 [英] SQL complex dynamic Pivoting 2

问题描述

我正在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;

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