如何为以下查询进行数据透视? [英] How to do Pivoting for the below query?

问题描述

我的记录设置如下

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)在以下情况下失败

b) Failed for the below case

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 --

推荐答案

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

This will help in case for dynamic levels:

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

这篇关于如何为以下查询进行数据透视?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！