How to do Pivoting for the below query?

Question

I have a record set as under

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

The desired output will be

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 Schema

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

Here is my attempt

;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

Disadvantages

a) Slow query

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

Actual

---

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

Expected

---

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

Answer 1

I'm able to achieve the desired output, considering you're not going to have more Levels than [L5] in that case you need to have dynamic PIVOT solution. For Dynamic Pivot solution you can check Krishnaraj's answer.

[Live Demo]

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;

Answer 2

Sample data to get the result

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 

Dynamic sql process to get the result

    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

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