sql-server - 旋转或逆旋转

Question

我有一个包含每小时统计数据的表格，我需要对其进行透视(或反透视？)

起始表的形式是:

[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。我了解数据透视表的基础知识，但对它们没有太多经验。它的 [Statistic] 列让我不知所措。

Answer 1

这是一种无需数据透视表即可获得这些结果的方法，根据索引和数据大小，它可能会更快:

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