如何为多列 SQLServer 取消透视

动态查询取消透视： 使用 Sys.列和资料（动态查询）串联 |将多个列（需要追加）值分组到单个列中。 示例 S1、S2、S3...作为 SNO 稍后使用 Unpivot 处理动态查询，以将多列数据转换为单行。示例 D1、D2、D3......在带有 ID 的列下，以使用静态数据进行映射。 动态查询结果存储在表变量中，以便以后处理。 创建的表变量与 CTE 一起使用，将未透视数据与静态数据联接，以获得最终结果。

If OBJECT_ID('UnpivotData','u') is not null
     Drop table UnPivotData
    go
    Create table UnPivotData
    (
    Id int,
    BILLNO int,
    Cus_Name varchar(10),
    [Date] date,
    Tot_Bal Int,
    S1 smallint,
    S2 smallint,
    S3 smallint,
    S4 smallint,
    D1 varchar(3),
    D2 varchar(3),
    D3 varchar(3),
    D4 varchar(3),
    Q1 smallint,
    Q2 smallint,
    Q3 smallint,
    Q4 smallint,
    U1 smallint,
    U2 smallint,
    U3 smallint,
    U4 smallint,
    T1 smallint,
    T2 smallint,
    T3 smallint,
    T4 smallint,
    Total int,
    CusCode varchar(2)
    )

    insert into UnPivotData
    Select 29, 1, 'XXX', '03-04-2017', 1932, 1, 2, 3, NULL, 'AAA', 'BBB', 'CCC', NULL, 6, 30, 6, NULL, 80, 35, 67, NULL, 480, 1050, 402, 0, 1932, 'DF'
    union
    Select 40, 2,'YYY', '04-04-2017', 6454, 1, 2, 3, NULL, 'AAA', 'DDD', 'FFF', NULL, 30, 24, 50, NULL, 80, 96, 35, NULL, 2400, 2304, 1750, 0, 6454, 'MQ'


    DECLARE @SNO AS NVARCHAR(MAX)='',@PDT  AS NVARCHAR(MAX)='',@QTY  AS NVARCHAR(MAX)='', @UNIT  AS NVARCHAR(MAX)='', @TOT  AS NVARCHAR(MAX)=''

    SET @SNO = STUFF((SELECT distinct ',' + QuoteName(C.name)
                from sys.tables T join sys.columns C on T.object_id=C.object_id and T.object_id=OBJECT_ID('UnPivotData','U') and C.Name like'S'+'[0-9]%'
                 FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)'),1,1,'')
    SET @PDT = STUFF((SELECT distinct ',' + QuoteName(C.name)
                from sys.tables T join sys.columns C on T.object_id=C.object_id and T.object_id=OBJECT_ID('UnPivotData','U') and C.Name like'D'+'[0-9]%'
                 FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)'),1,1,'')
    SET @QTY = STUFF((SELECT distinct ',' + QuoteName(C.name)
               from sys.tables T join sys.columns C on T.object_id=C.object_id and T.object_id=OBJECT_ID('UnPivotData','U') and C.Name like'Q'+'[0-9]%'
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)'),1,1,'')
    SET @UNIT = STUFF((SELECT distinct ',' + QuoteName(C.name)
                from sys.tables T join sys.columns C on T.object_id=C.object_id and T.object_id=OBJECT_ID('UnPivotData','U') and C.Name like'U'+'[0-9]%'
                 FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)'),1,1,'')
    SET @TOT = STUFF((SELECT distinct ',' + QuoteName(C.name)
                from sys.tables T join sys.columns C on T.object_id=C.object_id and T.object_id=OBJECT_ID('UnPivotData','U') and C.Name like'T'+'[0-9]%'
                 FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)'),1,1,'')

    Declare @Query1 Nvarchar(MAX),@Query2 Nvarchar(MAX),@Query3 Nvarchar(MAX),@Query4 Nvarchar(MAX),@Query5 Nvarchar(MAX)

    Set @Query1='select ID,SNO  from
         (
         Select ID,'+@SNO+' from UnPivotData
         )L
         unpivot
         (
            SNo for col in('+ @SNO +')
         ) u'

    Set @Query2='select ID,PDT  from
         (
         Select ID,'+@PDT+' from UnPivotData
         )L
         unpivot
         (
            PDT for col in('+ @PDT +')
         ) u'

    Set @Query3='select ID,QTY  from
         (
         Select ID,'+@QTY+' from UnPivotData
         )L
         unpivot
         (
            QTY for col in('+ @QTY +')
         ) u'

    Set @Query4='select ID,UNIT  from
         (
         Select ID,'+@UNIT+' from UnPivotData
         )L
         unpivot
         (
            UNIT for col in('+ @UNIT +')
         ) u'

    Set @Query5='select ID, TOT  from
        (
        Select ID,'+@TOT+' from UnPivotData
        )L
        unpivot
        (
        TOT for col in('+ @TOT +')
        ) u'


    Declare @t1 table
    (
    id int,
    SNO Smallint
    )
    Declare @t2 table
    (
    id int,
    PDT Varchar(3)
    )
    Declare @t3 table
    (
    id int,
    QTY Smallint
    )
    Declare @t4 table
    (
    id int,
    UNIT Smallint
    )
    Declare @t5 table
    (
    id int,
    TOT Smallint
    )
    Insert into @T1 execute ( @Query1 )
    Insert into @T2 execute ( @Query2 )
    Insert into @T3 execute ( @Query3 )
    Insert into @T4 execute ( @Query4 )
    Insert into @T5 execute ( @Query5 )


    ;With Cte
    as
    (
    Select UD.Id,BILLNO,Cus_Name,[Date],Tot_Bal,SNO,Row_Number() over(partition by T1.ID Order by T1.ID)Row_NO--,PDT,QTY,UNIT,TOT
    ,Total,CusCode
    from UnPivotData UD join @t1 T1  on UD.Id=T1.id
    )
    ,
    Cte1
    as
    (
    Select Cte.*,PDT from Cte Join
    (Select *,Row_Number() over(partition by ID Order by ID)Row_NO from  @t2) T2 on Cte.ID=T2.ID and Cte.Row_NO=T2.Row_NO
    )
    ,
    Cte2
    as
    (
    Select Cte1.*,QTY from Cte1 Join
    (Select *,Row_Number() over(partition by ID Order by ID)Row_NO from  @t3) T3 on Cte1.ID=T3.ID and Cte1.Row_NO=T3.Row_NO
    )
    ,
    Cte3
    as
    (
    Select Cte2.*,UNIT from Cte2 Join
    (Select *,Row_Number() over(partition by ID Order by ID)Row_NO from  @t4) T4 on Cte2.ID=T4.ID and Cte2.Row_NO=T4.Row_NO
    )
    ,
    Cte4
    as
    (
    Select Cte3.*,TOT from Cte3 Join
    (Select *,Row_Number() over(partition by ID Order by ID)Row_NO from  @t5) T5 on Cte3.ID=T5.ID and Cte3.Row_NO=T5.Row_NO
    )
    Select Id,BILLNO,Cus_Name,[Date],Tot_Bal,SNO,PDT,QTY,UNIT,TOT
    ,Cuscode
     from Cte4