Pivot or unpivot

Question

I have a table with hourly statistics that I need to pivot (or unpivot?)

The starting table is of the form:

[SystemID] [Hour] [CallStarts] [AvgDuration]  
    1         0        3           27
    1         1       10           58
    1         2       43           45
    1         3       54           63
    2         0        6           56
    2         1       46           98
    2         2       56           67
    2         3       65           77

And I need the output to be:

[SystemID] [Statistic] [Hr0] [Hr1] [Hr2] [Hr3]  
     1     CallStarts    3    10     43    54  
     1     AvgDuration  27    58     45    63
     2     CallStarts    6    46     56    65
     2     AvgDuration  56    98     67    77

I'm using SQL Server 2008R2. I understand the basics of pivot tables but I don't have much experience with them. Its the [Statistic] column that has me for a loss.

score 1 · Answer 1 · edited Mar 30 '14 at 04:39

1

You have the right idea... unpivot and then pivot. Maybe there is a better way, but this works:

SELECT *
FROM (
    SELECT SystemId, [Hour], [Stat], [Statistics]
    FROM
        (SELECT systemid, [hour], CallStarts, AvgDuration FROM t) nt
    UNPIVOT
        (stat FOR [Statistics] IN (CallStarts, AvgDuration)) AS ut
    ) AS ap
PIVOT
    (SUM (Stat) FOR [Hour] IN ([0], [1], [2], [3], [4])) AS p
ORDER BY [SystemId], [Statistics] DESC

Here is a sqlfiddle showing it working: http://www.sqlfiddle.com/#!3/eb109/8/0

edited Mar 30 '14 at 04:39

Ghasan غسان

5,577
4
33
44

answered Mar 29 '14 at 02:33

attila

2,219
1
11
15

Thank you, this is correct. You might want to add `order by [systemid]` at the end. – Erran Morad Mar 29 '14 at 02:53

score 1 · Answer 2 · answered Mar 30 '14 at 04:05

You can query each type of the statistics alone, and then have them unioned. I used the same schema built by @attila on SQL Fiddle.

WITH callS AS (SELECT systemid, hour, callstarts FROM t),
callD AS (SELECT systemid, hour, avgduration FROM t)

SELECT systemid, 'callStarts' AS [statistics], [0] as Hr0, [1] as Hr1, [2] as Hr2, [3] AS Hr3
FROM callS pivot (max(callStarts) FOR hour IN ([0], [1], [2], [3])) AS p

UNION ALL

SELECT systemid, 'AvgDuration' AS [statistics], [0], [1], [2], [3]
FROM callD pivot (max(avgduration) FOR hour IN ([0], [1], [2], [3])) AS p2

ORDER BY systemid, [statistics] DESC;

The output can be seen seen here: http://www.sqlfiddle.com/#!3/eb109/20/0

score 1 · Accepted Answer · answered Mar 30 '14 at 04:50

Here is a way to get these results without pivot, depending on indexes and data size it may be faster:

SELECT SystemID, 
       MAX('CallStarts') AS Statistic, 
       SUM(CASE WHEN Hour = 0 THEN CallStarts ELSE 0 END) AS Hr0,
       SUM(CASE WHEN Hour = 1 THEN CallStarts ELSE 0 END) AS Hr1,
       SUM(CASE WHEN Hour = 2 THEN CallStarts ELSE 0 END) AS Hr2,
       SUM(CASE WHEN Hour = 3 THEN CallStarts ELSE 0 END) AS Hr3
FROM Table
GROUP BY SystemID
  UNION ALL
SELECT SystemID, 
       MAX('AvgDuration') AS Statistic, 
       SUM(CASE WHEN Hour = 0 THEN AvgDuration ELSE 0 END) AS Hr0,
       SUM(CASE WHEN Hour = 1 THEN AvgDuration ELSE 0 END) AS Hr1,
       SUM(CASE WHEN Hour = 2 THEN AvgDuration ELSE 0 END) AS Hr2,
       SUM(CASE WHEN Hour = 3 THEN AvgDuration ELSE 0 END) AS Hr3
FROM Table
GROUP BY SystemID
ORDER BY SystemID, Statistic ASC

Thanks, these have all been very helpful. I decided to go with the union method because it is easier to maintain in the future by someone in the field. Performance is not an issue. Also, as I've been digging into the situation, it's clear that some of the data needs additional manipulation so I can't use a straight pivot. — Dino, Apr 01 '14 at 18:52

Pivot or unpivot

3 Answers3