SQL Server 行列转换

报表显示需求，查询结果往往需要做一些行列转换或列行转换来显示。

就以http://www.cnblogs.com/insus/articles/1968148.html这个例子的数据源做演示。

正常查询结果显示和执行结果，如下：

SELECT [RId],[DT],[Hits] FROM [dbo].[RecordHits]

 

 

下面演示，把[RId]和[DT]作为列显示：

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]

 

执行结果：

 

下面把DT,记录[RId]作为列显示：

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]

 

执行显示结果：