CODEWITHSUNDEEP
sqlsql-server
Wolfy
Wolfy

Reputation: 458

Pivot dates as rows

I have something like this:

enter image description here

From this query

SELECT DISTINCT Securitization, SUM(RemittableCollections) [RemittableCollections], ReportingDate
FROM Securitization.dbo.SecuritizationReporting
GROUP BY Securitization,ReportingDate

What I want is the Reporting dates to be on the rows, so I will have securitzation as the columns and then for each reporting date I will have a sum of Remittable collections, how can I do this?

This is what I am trying to do but it doesnt work

SELECT DISTINCT Securitization, ReportingDate
FROM Securitization.dbo.SecuritizationReporting
PIVOT
(
 SUM(RemittableCollections)
 for dates in (SELECT DISTINCT ReportingDate FROM Securitization.dbo.SecuritizationReporting )
)
GROUP BY Securitization,ReportingDate

Upvotes: 2

Views: 75

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 82020

Untested, but perhaps this will help

Declare @SQL varchar(max) = '
Select *
 From  (Select Distinct 
               Securitization
              ,ReportingDate
              ,RemittableCollections
         From  Securitization.dbo.SecuritizationReporting 
       ) src
 Pivot ( sum(RemittableCollections) for ReportingDate in ( ' + Stuff((Select Distinct 
                                                                             ',' + QuoteName(ReportingDate) 
                                                                       From  Securitization.dbo.SecuritizationReporting 
                                                                       Order By 1 
                                                                       For XML Path('')),1,1,'')  +' ) ) pvt
'
--Print(@SQL)
Exec(@SQL)

EDIT - Remove NULLS

Declare @NoNulls varchar(max) = Stuff( (
Select Distinct 
       ',' + QuoteName(ReportingDate) +' = IsNull(' + QuoteName(ReportingDate) +',0)'
 From  Securitization.dbo.SecuritizationReporting  
 Order By 1 
 For XML Path('')),1,1,'')

Declare @SQL varchar(max) = '
Select [Securitization]
      ,' + @NoNulls + '
 From  (Select Distinct 
               Securitization
              ,ReportingDate
              ,RemittableCollections
         From  Securitization.dbo.SecuritizationReporting 
       ) src
 Pivot ( sum(RemittableCollections) for ReportingDate in ( ' + Stuff((Select Distinct 
                                                                             ',' + QuoteName(ReportingDate) 
                                                                       From  Securitization.dbo.SecuritizationReporting 
                                                                       Order By 1 
                                                                       For XML Path('')),1,1,'')  +' ) ) pvt
'
--Print(@SQL)
Exec(@SQL)

Upvotes: 2

Related Questions