CODEWITHSUNDEEP
sqlsql-servert-sql
marco debala
marco debala

Reputation: 109

Pivot in SQL Server T-SQL

I have this query using Pivot

Select *
From  (
         Select Quarters = concat('Quarter',DATEPART(QUARTER,EndDate))
               ,Item     = [Name]
               ,Value    = count(*)
          From  TS
          Group By DATEPART(QUARTER,EndDate),[Name]
        Union All
        Select Quarters
              ,Item
              ,Value = 0
         From ( values ('Quater1')
                      ,('Quater2')
                      ,('Quater3')
                      ,('Quater4')
              ) A(Quarters)
         Cross Join ( Select distinct Item=[Name] from TS)  B
       ) src
Pivot ( sum(Value) for Item in ([Status1],[Status2],[Status3],[Status4]) ) pvt

And I need in the select to get data like in this example

Quarters    Status1 Status2 Status3 Status4
Quater1      0       0        0     0
Quater2      0       0        0     0
Quater3      0       0        0     0
Quater4      0       0        0     0

But the data selecting like this example

Quarters    Status1 Status2 Status3 Status4
Quarter1    NULL    1        NULL   1
Quarter3    NULL    NULL     NULL   1
Quarter4    NULL    NULL     NULL   2
Quater1      0       0        0     0
Quater2      0       0        0     0
Quater3      0       0        0     0
Quater4      0       0        0     0

Someone have any suggestion?

Upvotes: 1

Views: 47

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81930

Fixed Typo Quater1 vs Quarter1 (missing the R)

Select *
 From  (
         Select Quarters = concat('Quarter',DATEPART(QUARTER,EndDate))
               ,Item     = [Name]
               ,Value    = count(*)
          From  TS
          Group By DATEPART(QUARTER,EndDate),[Name]
        Union All
        Select Quarters
              ,Item
              ,Value = 0
         From ( values ('Quarter1')
                      ,('Quarter2')
                      ,('Quarter3')
                      ,('Quarter4')
              ) A(Quarters)
         Cross Join ( Select distinct Item=[Name] from TS)  B
       ) src
 Pivot ( sum(Value) for Item in ([Status1],[Status2],[Status3],[Status4]) ) pvt

Upvotes: 2

Related Questions