我有一个每小时的统计数据表,我需要pivot(或unpivot?)
起始表的格式为:
[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
我需要的输出是:
[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
我正在使用SQL Server 2008R2。我了解数据透视表的基本知识,但我没有太多的经验。是[统计]专栏让我吃亏。
这里有一种不需要枢轴的方法,根据索引和数据大小,它可能更快:
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
您可以单独查询每种类型的统计信息,然后将它们合并。我在SQL Fiddle上使用了@attila构建的相同模式。
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;
输出如下所示:http://www.sqlfiddle.com/#!3/eb109/20/0
你的想法是对的…旋转,然后旋转。也许有更好的方法,但这是可行的:
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
下面是一个sqlfiddle显示它工作:http://www.sqlfiddle.com/#!3/eb109/8/0