SQL Server 行列转换
报表显示需求,查询结果往往需要做一些行列转换或列行转换来显示。
就以http://www.cnblogs.com/insus/articles/1968148.html这个例子的数据源做演示。
正常查询结果显示和执行结果,如下:
SELECT [RId],[DT],[Hits] FROM [dbo].[RecordHits]
下面演示,把[RId]和[DT]作为列显示:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT [RID],
SUM(CASE WHEN [DT] = '2011-01-23' THEN [Hits] END) AS '2011-01-23',
SUM(CASE WHEN [DT] = '2011-01-24' THEN [Hits] END) AS '2011-01-24',
SUM(CASE WHEN [DT] = '2011-01-25' THEN [Hits] END) AS '2011-01-25',
SUM(CASE WHEN [DT] = '2011-01-26' THEN [Hits] END) AS '2011-01-26'
FROM [dbo].[RecordHits]
GROUP BY [RId]
SUM(CASE WHEN [DT] = '2011-01-23' THEN [Hits] END) AS '2011-01-23',
SUM(CASE WHEN [DT] = '2011-01-24' THEN [Hits] END) AS '2011-01-24',
SUM(CASE WHEN [DT] = '2011-01-25' THEN [Hits] END) AS '2011-01-25',
SUM(CASE WHEN [DT] = '2011-01-26' THEN [Hits] END) AS '2011-01-26'
FROM [dbo].[RecordHits]
GROUP BY [RId]
执行结果:
下面把DT,记录[RId]作为列显示:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT [DT],
SUM(CASE WHEN [RId] = 'R1' THEN [Hits] END) AS 'R1',
SUM(CASE WHEN [RId] = 'R2' THEN [Hits] END) AS 'R2',
SUM(CASE WHEN [RId] = 'R3' THEN [Hits] END) AS 'R3',
SUM(CASE WHEN [RId] = 'R4' THEN [Hits] END) AS 'R4'
FROM [dbo].[RecordHits]
GROUP BY [DT]
SUM(CASE WHEN [RId] = 'R1' THEN [Hits] END) AS 'R1',
SUM(CASE WHEN [RId] = 'R2' THEN [Hits] END) AS 'R2',
SUM(CASE WHEN [RId] = 'R3' THEN [Hits] END) AS 'R3',
SUM(CASE WHEN [RId] = 'R4' THEN [Hits] END) AS 'R4'
FROM [dbo].[RecordHits]
GROUP BY [DT]
执行显示结果: