All geek questions in one place

How to make a turn for the request below?

I have a set of records 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 result 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 request

b) Failed to complete the case below

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

Thanks for your support in advance.

+1
source share
3 answers

This will help in case of 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
+1
source

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)
+1
source

, , , [L5], PIVOT. Dynamic Pivot .

[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;
+1

Source: https://habr.com/ru/post/1684187/

More articles:


All Articles