CODEWITHSUNDEEP
sqlsql-serverselectpivotpivot-table
Abdalla Omar
Abdalla Omar

Reputation: 75

Converting Integer Rows into Columns While Grouping by Date

I have the following table with these columns TimeStamp[DateTime], FromSysId[Int], CrUSSDCnt[Int]

       TimeStamp           FromSysId          CrUSSDCnt
2015-06-18 18:58:15.380        25                 7
2015-06-18 19:45:17.130        21                 8
2015-06-18 20:43:18.920        22                 15
2015-06-18 21:34:33.090        25                 6
2015-06-18 22:55:33.317        22                 10

And I want to display each FromSysId as Column while counting the CrUSSDCnt column of each FromSysId and grouping TimeStime by Hour like this output:

       TimeStamp         Sys25   Sys22     Sys21       
2015-06-18 18:00:00       2        1         0
2015-06-18 19:00:00       4        6         3
2015-06-18 20:00:00       0        5         6
2015-06-18 21:00:00       0        7         2
2015-06-18 22:00:00       1        0         4

I tried using pivot or Joint and i failed both to summarize like this way. Any help.

Upvotes: 1

Views: 51

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

The hard part of this problem is extracting the hour from the time. Here is one method:

select dateadd(hour, 0, datediff(hour, 0, timestamp)) as ToTheHour,
       sum(case when FromSysId = 25 then CrUSSDCnt else 0 end) as Sys25,
       sum(case when FromSysId = 22 then CrUSSDCnt else 0 end) as Sys22,
       sum(case when FromSysId = 21 then CrUSSDCnt else 0 end) as Sys21
from table t
group by dateadd(hour, 0, datediff(hour, 0, timestamp))
order by ToTheHour;

The rest is just conditional aggregation.

Upvotes: 1

Related Questions