SQL Server 行转列的实现（横排）

最新推荐文章于 2024-04-26 17:25:20 发布

无幻

最新推荐文章于 2024-04-26 17:25:20 发布

阅读量3.5k

点赞数

5.5 SQL Server 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

SQL Server 2000：

在一些统计报表中，常常会用到将行结果用列形式展现。我们这里用一个常见的学生各门课程的成绩报表，来实际展示实现方法。

我们用到的表结构如下：

三张表的关系为：

现有的测试数据为：

我们需要的结果是：

SQL语句如下：

DECLARE @strSQL VARCHAR( 8000)
SET @strSQL = 'SELECT t.STUNAME [姓名]'
SELECT @strSQL = @strSQL + ',SUM(CASE s.SNAME WHEN ' '' + SNAME + '' ' THEN g.[Score] END) [' + SNAME + ']' FROM ( SELECT SNAME FROM [Subject]) AS tmp
SELECT @strSQL = @strSQL + ' FROM [Score] g,[Subject] s, [Student] t WHERE g.SID=s.SID AND g.STUID = t.STUID GROUP BY t.STUID, t.STUNAME'
EXEC(@strSQL)

SQL SERVER 2005 中，已经有实现此功能的内置方法了。

SQL SERVER 2005中新增加了两个关系运算符 PIVOT/ UNPIVOT，能够实现表中的列转换到行，以及行到列的转换工作。
举例，还是先创建测试数据表:

CREATE TABLE sales.salesByMonth
(
year char( 4),
month char( 3),
amount money,
PRIMARY KEY (year, month)
)

INSERT INTO sales.salesByMonth (year, month, amount)
VALUES( '2004', 'Jan', 789. 0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES( '2004', 'Feb', 389. 0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES( '2004', 'Mar', 8867. 0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES( '2004', 'Apr', 778. 0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES( '2004', 'May', 78. 0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES( '2004', 'Jun', 9. 0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES( '2004', 'Jul', 987. 0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES( '2004', 'Aug', 866. 0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES( '2004', 'Sep', 7787. 0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES( '2004', 'Oct', 85576. 0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES( '2004', 'Nov', 855. 0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES( '2004', 'Dec', 5878. 0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES( '2005', 'Jan', 7. 0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES( '2005', 'Feb', 6868. 0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES( '2005', 'Mar', 688. 0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES( '2005', 'Apr', 9897. 0000)

我们想要得到类似这样的结果：

Year Jan Feb Mar …………..

—– ———- ———– ———–

2004 789.0000 389.0000 8867.0000 ………….

2005 7.0000 6868.0000 688.0000 …………..

用上面介绍的方法当然可以实现，但现在这里想要的列是固定的，不是动态的，就是12个月，所以也可以这样子来用：

SELECT year,
SUM(case when month = 'Jan' then amount else 0 end) AS 'Jan',
SUM(case when month = 'Feb' then amount else 0 end) AS 'Feb',
SUM(case when month = 'Mar' then amount else 0 end) AS 'Mar',
SUM(case when month = 'Apr' then amount else 0 end) AS 'Apr',
SUM(case when month = 'May' then amount else 0 end) AS 'May',
SUM(case when month = 'Jun' then amount else 0 end) AS 'Jun',
SUM(case when month = 'Jul' then amount else 0 end) AS 'Jul',
SUM(case when month = 'Aug' then amount else 0 end) AS 'Aug',
SUM(case when month = 'Sep' then amount else 0 end) AS 'Sep',
SUM(case when month = 'Oct' then amount else 0 end) AS 'Oct',
SUM(case when month = 'Nov' then amount else 0 end) AS 'Nov',
SUM(case when month = 'Dec' then amount else 0 end) AS 'Dec'
FROM sales.salesByMonth
GROUP by year

但这样事实上还是相当麻烦的，现在SQLSERVER2005中有更方便的实现方法。

SELECT Year,[Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec]
FROM (
SELECT year, amount, month
FROM sales.salesByMonth ) AS salesByMonth
PIVOT ( SUM(amount) FOR month IN
([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])
) AS ourPivot
ORDER BY Year

就是这样，很简单的用法，效果是完全一样的。

我们再尝试一下把year去掉：

SELECT [Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec]
FROM ( SELECT amount, month
FROM sales.salesByMonth ) AS salesByMonth
PIVOT ( SUM(amount) FOR month IN
([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])
) AS ourPivot

得到的结果是：

Jan Feb Mar …

———- ———— ———–

796.0000 7257.0000 9555.0000 …

同一个月份的数据累加到一起。

再给个微软官方的例子：