Converting Integer Rows into Columns While Grouping by Date

Question

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.

Please edit your question with your queries that don't work. — Gordon Linoff
– Gordon Linoff, Commented Jun 21, 2015 at 14:04

Gordon Linoff · Accepted Answer · 2015-06-21 15:20:19Z

1

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.

edited Jun 21, 2015 at 15:20

answered Jun 21, 2015 at 14:06

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gordon Linoff Over a year ago

@AbdallaOmar . . . If you have a question, you should ask it as a question not an answer. Ask another question, if you still need help.

Collectives™ on Stack Overflow

Converting Integer Rows into Columns While Grouping by Date

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related