Regarding SQL Pivot

Question

Hi I have been trying to do a PIVOT for the following data.

LeaseId | ColumnHeader  | UIPayments | UIResidual
-------------------------------------------------
25573   | 2019-05-01    | 0.0000     | 0.0000    
25573   | 2019-06-01    | 0.0000     | 0.0000    
25573   | 2019-07-01    | 0.0000     | 0.0000    
25573   | 2019-08-01    | 0.0000     | 0.0000    
25573   | 2019-09-01    | 0.0000     | 0.0000    
25573   | 2019-10-01    | 0.0000     | 0.0000   

The result dataset should look like:

LeaseId |Details    | 2019-05-01| 2019-06-01|2019-07-01 |2019-08-01
-----------------------------------------------------------------                
25573   |UIPayments |5.0000     |5.0000     |5.0000     |5.0000

25573   |UIResidual |1.0000     |1.0000     |1.0000     |1.0000

Is it possible?

Thanks in advance.

The query I have tried so far:

DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',[' + CAST(DATEFROMPARTS(#TempMonths.y, #TempMonths.m, '01') AS VARCHAR(20)) + ']'
                from #TempMonths
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

set @query = 'SELECT LeaseID, UIPayments, UIResidual, PVResidual, PVPayments, NetInvestment,' + @cols + ' from 
         (
            SELECT * FROM #TmpDetails
        ) x
        pivot 
        (
            UIPayments, UIResidual, PVResidual, PVPayments, NetInvestment
            for ColumnHeader in (' + @cols + ')
        ) p '

I really don't know if this is even a valid query.

Thanks in advance!

Answer 1

Conditional aggregation should work in almost any database:

select LeaseId, 'UIPayments' as details,
       sum(case when ColumnHeader = '2019-05-01' then UIPayments else 0 end) as val_20190501,
       sum(case when ColumnHeader = '2019-06-01' then UIPayments else 0 end) as val_20190601,
       sum(case when ColumnHeader = '2019-07-01' then UIPayments else 0 end) as val_20190701,
       sum(case when ColumnHeader = '2019-08-01' then UIPayments else 0 end) as val_20190801
from t
group by LeaseId
union all
select LeaseId, 'UIResidual' as details,
       sum(case when ColumnHeader = '2019-05-01' then UIResidual else 0 end) as val_20190501,
       sum(case when ColumnHeader = '2019-06-01' then UIResidual else 0 end) as val_20190601,
       sum(case when ColumnHeader = '2019-07-01' then UIResidual else 0 end) as val_20190701,
       sum(case when ColumnHeader = '2019-08-01' then UIResidual else 0 end) as val_20190801
from t
group by LeaseId;

I guess the trick is that you need to handle each column separately. In many databases, you could do this using a lateral join, rather than union all.