如何对以下查询进行旋转？

Question

我有如下记录

AGREEMENTID     FeedbackDate            DispositionCode
0003SBML00151   2017-03-08 00:00:00.000 PTP
0004SBHL00705   2017-03-17 00:00:00.000 BPTP
0007SBML01987   NULL                    NULL
0026MSS00108    2017-05-20 00:00:00.000 PTP
0026MSS00108    2017-03-22 00:00:00.000 PTP
0026MSS00108    2016-12-30 00:00:00.000 BPTP
0026MSS00108    2016-12-29 00:00:00.000 BPTP
0026MSS00108    2016-12-28 00:00:00.000 BPTP
0037SBHL02361   NULL                    NULL
0038SBML00291   2017-05-04 00:00:00.000 PTP
0038SBML00291   2017-04-24 00:00:00.000 BPTP
0038SBML00291   2017-04-11 00:00:00.000 NC
0038SBML00291   2016-12-22 00:00:00.000 PTP
0038SBML00291   2016-12-09 00:00:00.000 DC

期望的输出将是

AGREEMENTID     L1      L2      L3      L4      L5
0003SBML00151   PTP     NULL    NULL    NULL    NULL
0004SBHL00705   BPTP    NULL    NULL    NULL    NULL
0007SBML01987   NULL    NULL    NULL    NULL    NULL
0026MSS00108    PTP     PTP     BPTP    BPTP    BPTP
0037SBHL02361   NULL    NULL    NULL    NULL    NULL
0038SBML00291   PTP     BPTP    NC      PTP     DC

SQL模式

Declare @T table(AGREEMENTID varchar(50),FeedbackDate varchar(50),DispositionCode varchar(10))
Insert into @T 
Select '0003SBML00151','2017-03-08 00:00:00.000','PTP' union all
Select '0004SBHL00705','2017-03-17 00:00:00.000','BPTP' union all
Select '0007SBML01987',NULL,NULL union all
Select '0026MSS00108','2017-05-20 00:00:00.000','PTP' union all
Select '0026MSS00108','2017-03-22 00:00:00.000','PTP' union all
Select '0026MSS00108','2016-12-30 00:00:00.000','BPTP' union all
Select '0026MSS00108','2016-12-29 00:00:00.000','BPTP' union all
Select '0026MSS00108','2016-12-28 00:00:00.000','BPTP' union all
Select '0037SBHL02361',NULL,NULL union all
Select '0038SBML00291','2017-05-04 00:00:00.000','PTP' union all
Select '0038SBML00291','2017-04-24 00:00:00.000','BPTP' union all
Select '0038SBML00291','2017-04-11 00:00:00.000','NC' union all
Select '0038SBML00291','2016-12-22 00:00:00.000','PTP' union all
Select '0038SBML00291','2016-12-09 00:00:00.000','DC'

Select *
From @T

这是我的尝试

;with cte1 as(

SELECT AGREEMENTID,  abc = STUFF(
             (SELECT '.' + DispositionCode 
              FROM @T t1
              WHERE t1.AGREEMENTID = t2.AGREEMENTID --and t1.Rn = t2.Rn
              FOR XML PATH (''))
             , 1, 1, '') from @T t2
group by AGREEMENTID)

--select *
--from cte1
,cte2 as(

select AGREEMENTID,

    X= IIF(charindex('.',abc,1) is null,'NULL VALUE',IIF(charindex('.',abc,1) = 0,'SINGLE VALUE','MULTIPLE VALUE'))

        --,COL1 = SUBSTRING(abc,1,IIF(charindex('.',abc,1) = NULL,0,IIF(charindex('.',abc,1) = 0,len(abc),(charindex('.',abc,1)-1))))
        --,charindex('.',abc,1)
        ,abc
from cte1)
,cte3 as(
select 
    AGREEMENTID
    ,COL1 =IIF(X = 'NULL VALUE', NULL,IIF(X='SINGLE VALUE',SUBSTRING(abc,1,len(abc)),SUBSTRING(abc,1,(charindex('.',abc,1)-1))))
    ,abc
    ,OtherCols = IIF(X = 'MULTIPLE VALUE',SUBSTRING(abc,charindex('.',abc,1)+1,len(abc)),'')
from cte2)

select 
    AGREEMENTID 
    ,L1 = IIF(COL1 is null, '--',COL1)
    ,l2 = IIF(PARSENAME(OtherCols,4)is null, '--',PARSENAME(OtherCols,4))
    ,l3 = IIF(PARSENAME(OtherCols,3)is null, '--',PARSENAME(OtherCols,3))
    ,l4 = IIF(PARSENAME(OtherCols,2)is null, '--',PARSENAME(OtherCols,2))
    ,l5 = IIF(PARSENAME(OtherCols,1)is null, '--',PARSENAME(OtherCols,1))   
From cte3

缺点

a)查询速度慢

( b)在以下情况下失败

AGREEMENTID      FeedbackDate           DispositionCode
0002SBML01241   2017-05-04 00:00:00.000 Today
0002SBML01241   2017-04-24 00:00:00.000 PTP
0002SBML01241   2017-04-11 00:00:00.000 PTP
0002SBML01241   2016-12-22 00:00:00.000 PTP

实际

AGREEMENTID     L1      l2  l3  l4  l5
0002SBML01241   Today   --  PTP PTP PTP

期望的

 AGREEMENTID     L1     l2  l3  l4  l5
0002SBML01241   Today   PTP PTP PTP --

Answer 1

对于动态级别，这将有所帮助：

Declare @T table(AGREEMENTID varchar(50),FeedbackDate varchar(50),DispositionCode varchar(10))
Insert into @T 
Select '0003SBML00151','2017-03-08 00:00:00.000','PTP' union all
Select '0004SBHL00705','2017-03-17 00:00:00.000','BPTP' union all
Select '0007SBML01987',NULL,NULL union all
Select '0026MSS00108','2017-05-20 00:00:00.000','PTP' union all
Select '0026MSS00108','2017-03-22 00:00:00.000','PTP' union all
Select '0026MSS00108','2016-12-30 00:00:00.000','BPTP' union all
Select '0026MSS00108','2016-12-29 00:00:00.000','BPTP' union all
Select '0026MSS00108','2016-12-28 00:00:00.000','BPTP' union all
Select '0037SBHL02361',NULL,NULL union all
Select '0038SBML00291','2017-05-04 00:00:00.000','PTP' union all
Select '0038SBML00291','2017-04-24 00:00:00.000','BPTP' union all
Select '0038SBML00291','2017-04-11 00:00:00.000','NC' union all
Select '0038SBML00291','2016-12-22 00:00:00.000','PTP' union all
Select '0038SBML00291','2016-12-09 00:00:00.000','DC'

Select   'L'+convert(varchar(255),ROW_NUMBER()over(partition by AGREEMENTID order by AGREEMENTID))'rno',*
into test
From @T order by AGREEMENTID

declare @Levels nvarchar(max),@SQL NVARCHAR(MAX) 
select @Levels= STUFF((select DISTINCT ','+rno from test 
for xml path('')),1,1,'')

SET @SQL=
'SELECT AGREEMENTID,'+@Levels+'
FROM (select AGREEMENTID,DispositionCode,rno from test
)as TEMP'+'
PIVOT'+'
(MAX(DispositionCode)
FOR rno IN ('+@Levels+')
)AS pvt'

exec sp_executesql @SQL

Answer 2

获取结果的样本数据

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
DROP TABLE #Temp

Declare @T table
(AGREEMENTID varchar(50),
FeedbackDate varchar(50),
DispositionCode varchar(10)
)
Insert into @T 
SELECT '0003SBML00151','2017-03-08 00:00:00.000','PTP' union all
SELECT '0004SBHL00705','2017-03-17 00:00:00.000','BPTP' union all
SELECT '0007SBML01987',NULL,NULL union all
SELECT '0026MSS00108','2017-05-20 00:00:00.000','PTP' union all
SELECT '0026MSS00108','2017-03-22 00:00:00.000','PTP' union all
SELECT '0026MSS00108','2016-12-30 00:00:00.000','BPTP' union all
SELECT '0026MSS00108','2016-12-29 00:00:00.000','BPTP' union all
SELECT '0026MSS00108','2016-12-28 00:00:00.000','BPTP' union all
SELECT '0037SBHL02361',NULL,NULL union all
SELECT '0038SBML00291','2017-05-04 00:00:00.000','PTP' union all
SELECT '0038SBML00291','2017-04-24 00:00:00.000','BPTP' union all
SELECT '0038SBML00291','2017-04-11 00:00:00.000','NC' union all
SELECT '0038SBML00291','2016-12-22 00:00:00.000','PTP' union all
SELECT '0038SBML00291','2016-12-09 00:00:00.000','DC'

SELECT *, 
       'L' 
       + Cast(Row_number()OVER(partition BY dispositioncode ORDER BY agreementid 
       )AS 
       VARCHAR(10)) AS Lcolumn 
INTO   #temp 
FROM   @T 

动态sql进程以获得结果

    DECLARE @DyColumn NVARCHAR(max), @DyColumn2 NVARCHAR(max), 
        @Sql      NVARCHAR(max) 

SELECT @DyColumn = STUFF((SELECT DISTINCT ', ' + QUOTENAME(lcolumn) 
                          FROM   #temp 
                          FOR xml path ('')), 1, 2, '') 
SELECT @DyColumn2 = STUFF((SELECT DISTINCT ', ' + 'ISNULL('+QUOTENAME(lcolumn) +','+'''0'''+') AS '++QUOTENAME(lcolumn)
                          FROM   #temp 
                          FOR xml path ('')), 1, 2, '') 

SET @Sql=N' SELECT AGREEMENTID,' + @DyColumn2 
         + ' FROM ( SELECT * FROM #Temp ) AS Src  PIVOT  (  MIN(DispositionCode) FOR Lcolumn IN (' 
         + @DyColumn + ')  )AS Pvt ' 

PRINT @Sql 

EXEC (@Sql) 

Answer 3

我能够实现期望的输出，考虑到您的级别不会超过[L5]，在这种情况下，您需要有动态支点解决方案。对于动态透视解决方案，您可以检查克里希纳拉伊氏答案。

[现场演示]

SELECT *
FROM
(
Select AGREEMENTID, 
       DispositionCode,
       'L' + CAST(ROW_NUMBER() OVER(Partition By AGREEMENTID ORDER BY FeedbackDate DESC) AS VARCHAR(255)) as RowNum       
From @T
) as PivotSource
PIVOT
(
    MAX(DispositionCode) FOR RowNum IN ([L1], [L2], [L3], [L4], [L5])
) as Pvt;