Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# SQL Select Pivot?

Posted on 2013-06-21
Medium Priority
235 Views
I have a table with the following phone call data fro our sales team

CallDTTM                                              Minutes
2013-06-10 00:23:30.000                        21
etc...

What I want to get is the calls by hour total minutes

CallDate          00AM    01AM    02AM   etc...
06/10/2013       123        251      14
0
Question by:lrbrister
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2
• 2
• +1

LVL 9

Assisted Solution

gt2847c earned 1000 total points
ID: 39265844
This doesn't use the PIVOT keyword, but accomplishes the same thing.  I tend to like this format better as it is more portable between server versions...

``````SELECT CONVERT(VARCHAR, [CallDTTM], 110) AS [CallDate]
, ISNULL(SUM(CASE WHEN DATEPART(hour,[CallDTTM]) = 0 THEN [Minutes] END), 0) AS [00AM]
, ISNULL(SUM(CASE WHEN DATEPART(hour,[CallDTTM]) = 1 THEN [Minutes] END), 0) AS [01AM]
, ISNULL(SUM(CASE WHEN DATEPART(hour,[CallDTTM]) = 2 THEN [Minutes] END), 0) AS [02AM]
, ISNULL(SUM(CASE WHEN DATEPART(hour,[CallDTTM]) = 3 THEN [Minutes] END),  0)AS [03AM]
-- 4-21 skipped for brevity
, ISNULL(SUM(CASE WHEN DATEPART(hour,[CallDTTM]) = 22 THEN [Minutes] END), 0) AS [10PM]
, ISNULL(SUM(CASE WHEN DATEPART(hour,[CallDTTM]) = 23 THEN [Minutes] END), 0) AS [11PM]
FROM [MyTable]
GROUP BY CONVERT(VARCHAR, [CallDTTM], 110)
ORDER BY CONVERT(VARCHAR, [CallDTTM], 110)
``````

Gives output like this:
``````CallDate	00AM	01AM	02AM	03AM	10PM	11PM
06-09-2013	0	0	0	15	0	0
06-10-2013	21	10	0	0	0	0
``````
0

LVL 22

Expert Comment

ID: 39265848
SELECT CALLDATE, HOUR,  SUM(MINUTES) /60   -- For hours
GROUP BY CALLDATE
0

LVL 11

Accepted Solution

Louis01 earned 1000 total points
ID: 39265963
``````declare @CallsTable table(CallDTTM datetime, [Minutes] int);
insert into @CallsTable values ('2013-06-10 00:23:30.000', 1);
insert into @CallsTable values ('2013-06-10 00:24:30.000', 10);
insert into @CallsTable values ('2013-06-10 10:23:30.000', 2);
insert into @CallsTable values ('2013-06-10 12:23:30.000', 3);
insert into @CallsTable values ('2013-06-10 14:23:30.000', 4);
insert into @CallsTable values ('2013-06-11 00:23:30.000', 1);
insert into @CallsTable values ('2013-06-11 10:23:30.000', 2);
insert into @CallsTable values ('2013-06-11 12:23:30.000', 3);
insert into @CallsTable values ('2013-06-11 14:23:30.000', 4);

select CallDate
, [0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23]
from (select replace(left(CONVERT(varchar, t1.CallDTTM, 13), 11), ' ', '-') as CallDate
, datepart(hour,t1.CallDTTM) as HourFrom
, t1.[Minutes]
from @CallsTable t1
) p1
PIVOT (SUM([Minutes])
FOR HourFrom in ([0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23])
) as pt1
``````
0

Author Comment

ID: 39266084
Away from my DEV PC.  Will test when I get back
0

Author Comment

ID: 39266403
I would like to split points between gt2847c and Louis01

Any violent objections?

Both viable solutions and Louis01 answered with PIVOT even though a minute or so later
0

LVL 9

Expert Comment

ID: 39266408
No complaints here
0

LVL 11

Expert Comment

ID: 39266448
Fine by me
0

Author Closing Comment

ID: 39266458
Perfect guys.
Thanks
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
###### Suggested Courses
Course of the Month10 days, 15 hours left to enroll