SQL Server 行列转换

最新推荐文章于 2023-08-21 22:00:16 发布

weixin_33859844

最新推荐文章于 2023-08-21 22:00:16 发布

阅读量52

点赞数

本文介绍如何通过SQL查询实现报表数据的行列转换，包括将[RId]和[DT]字段转换为列显示的方法，并展示了具体的操作步骤及执行结果。

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

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

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

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

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

View Code

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]作为列显示：

View Code

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 ]

执行显示结果：