我们首先先通过一个老生常谈的例子,学生成绩表(下面简化了些)来形象了解下行转列
![](https://i-blog.csdnimg.cn/blog_migrate/cdec0645add3fc3c328197dda5c76203.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/81178cc93a2a3bb5048d90d76e7ec935.gif)
(
[ UserName ] NVARCHAR ( 20 ), -- 学生姓名
[ Subject ] NVARCHAR ( 30 ), -- 科目
[ Score ] FLOAT , -- 成绩
)
INSERT INTO [ StudentScores ] SELECT ' Nick ' , ' 语文 ' , 80
INSERT INTO [ StudentScores ] SELECT ' Nick ' , ' 数学 ' , 90
INSERT INTO [ StudentScores ] SELECT ' Nick ' , ' 英语 ' , 70
INSERT INTO [ StudentScores ] SELECT ' Nick ' , ' 生物 ' , 85
INSERT INTO [ StudentScores ] SELECT ' Kent ' , ' 语文 ' , 80
INSERT INTO [ StudentScores ] SELECT ' Kent ' , ' 数学 ' , 90
INSERT INTO [ StudentScores ] SELECT ' Kent ' , ' 英语 ' , 70
INSERT INTO [ StudentScores ] SELECT ' Kent ' , ' 生物 ' , 85
如果我想知道每位学生的每科成绩,而且每个学生的全部成绩排成一行,这样方便我查看、统计,导出数据
![](https://i-blog.csdnimg.cn/blog_migrate/cdec0645add3fc3c328197dda5c76203.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/81178cc93a2a3bb5048d90d76e7ec935.gif)
UserName,
MAX ( CASE Subject WHEN ' 语文 ' THEN Score ELSE 0 END ) AS ' 语文 ' ,
MAX ( CASE Subject WHEN ' 数学 ' THEN Score ELSE 0 END ) AS ' 数学 ' ,
MAX ( CASE Subject WHEN ' 英语 ' THEN Score ELSE 0 END ) AS ' 英语 ' ,
MAX ( CASE Subject WHEN ' 生物 ' THEN Score ELSE 0 END ) AS ' 生物 '
FROM dbo. [ StudentScores ]
GROUP BY UserName
查询结果如图所示,这样我们就能很清楚的了解每位学生所有的成绩了
接下来我们来看看第二个小列子。有一个游戏玩家充值表(仅仅为了说明,举的一个小例子),
代码
(
[ ID ] INT IDENTITY ( 1 , 1 ),
[ UserName ] NVARCHAR ( 20 ), -- 游戏玩家
[ CreateTime ] DATETIME , -- 充值时间
[ PayType ] NVARCHAR ( 20 ), -- 充值类型
[ Money ] DECIMAL , -- 充值金额
[ IsSuccess ] BIT , -- 是否成功 1表示成功, 0表示失败
CONSTRAINT [ PK_Inpours_ID ] PRIMARY KEY (ID)
)
INSERT INTO Inpours SELECT ' 张三 ' , ' 2010-05-01 ' , ' 支付宝 ' , 50 , 1
INSERT INTO Inpours SELECT ' 张三 ' , ' 2010-06-14 ' , ' 支付宝 ' , 50 , 1
INSERT INTO Inpours SELECT ' 张三 ' , ' 2010-06-14 ' , ' 手机短信 ' , 100 , 1
INSERT INTO Inpours SELECT ' 李四 ' , ' 2010-06-14 ' , ' 手机短信 ' , 100 , 1
INSERT INTO Inpours SELECT ' 李四 ' , ' 2010-07-14 ' , ' 支付宝 ' , 100 , 1
INSERT INTO Inpours SELECT ' 王五 ' , ' 2010-07-14 ' , ' 工商银行卡 ' , 100 , 1
INSERT INTO Inpours SELECT ' 赵六 ' , ' 2010-07-14 ' , ' 建设银行卡 ' , 100 , 1
![](https://i-blog.csdnimg.cn/blog_migrate/cdec0645add3fc3c328197dda5c76203.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/81178cc93a2a3bb5048d90d76e7ec935.gif)
CASE PayType WHEN ' 支付宝 ' THEN SUM ( Money ) ELSE 0 END AS ' 支付宝 ' ,
CASE PayType WHEN ' 手机短信 ' THEN SUM ( Money ) ELSE 0 END AS ' 手机短信 ' ,
CASE PayType WHEN ' 工商银行卡 ' THEN SUM ( Money ) ELSE 0 END AS ' 工商银行卡 ' ,
CASE PayType WHEN ' 建设银行卡 ' THEN SUM ( Money ) ELSE 0 END AS ' 建设银行卡 '
FROM Inpours
GROUP BY CreateTime, PayType
如图所示,我们这样只是得到了这样的输出结果,还需进一步处理,才能得到想要的结果
![](https://i-blog.csdnimg.cn/blog_migrate/cbfb39a44ea317deef9c7a3d5a535780.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/cdec0645add3fc3c328197dda5c76203.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/81178cc93a2a3bb5048d90d76e7ec935.gif)
SELECT
CreateTime,
ISNULL ( SUM ( [ 支付宝 ] ), 0 ) AS [ 支付宝 ] ,
ISNULL ( SUM ( [ 手机短信 ] ), 0 ) AS [ 手机短信 ] ,
ISNULL ( SUM ( [ 工商银行卡 ] ), 0 ) AS [ 工商银行卡 ] ,
ISNULL ( SUM ( [ 建设银行卡 ] ), 0 ) AS [ 建设银行卡 ]
FROM
(
SELECT CONVERT ( VARCHAR ( 10 ), CreateTime, 120 ) AS CreateTime,
CASE PayType WHEN ' 支付宝 ' THEN SUM ( Money ) ELSE 0 END AS ' 支付宝 ' ,
CASE PayType WHEN ' 手机短信 ' THEN SUM ( Money ) ELSE 0 END AS ' 手机短信 ' ,
CASE PayType WHEN ' 工商银行卡 ' THEN SUM ( Money ) ELSE 0 END AS ' 工商银行卡 ' ,
CASE PayType WHEN ' 建设银行卡 ' THEN SUM ( Money ) ELSE 0 END AS ' 建设银行卡 '
FROM Inpours
GROUP BY CreateTime, PayType
) T
GROUP BY CreateTime
其实行转列,关键是要理清逻辑,而且对分组(Group by)概念比较清晰。上面两个列子基本上就是行转列的类型了。但是有个问题来了,上面是我为了说明弄的一个简单列子。实际中,可能支付方式特别多,而且逻辑也复杂很多,可能涉及汇率、手续费等等(曾经做个这样一个),如果支付方式特别多,我们的CASE WHEN 会弄出一大堆,确实比较恼火,而且新增一种支付方式,我们还得修改脚本如果把上面的脚本用动态SQL改写一下,我们就能轻松解决这个问题
![](https://i-blog.csdnimg.cn/blog_migrate/cdec0645add3fc3c328197dda5c76203.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/81178cc93a2a3bb5048d90d76e7ec935.gif)
DECLARE @tmpSql VARCHAR ( 8000 );
SET @cmdText = ' SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime, ' + CHAR ( 10 );
SELECT @cmdText = @cmdText + ' CASE PayType WHEN ''' + PayType + ''' THEN SUM(Money) ELSE 0 END AS ''' + PayType
+ ''' , ' + CHAR ( 10 ) FROM ( SELECT DISTINCT PayType FROM Inpours ) T
SET @cmdText = LEFT ( @cmdText , LEN ( @cmdText ) - 2 ) -- 注意这里,如果没有加CHAR(10) 则用LEFT(@cmdText, LEN(@cmdText) -1)
SET @cmdText = @cmdText + ' FROM Inpours GROUP BY CreateTime, PayType ' ;
SET @tmpSql = ' SELECT CreateTime, ' + CHAR ( 10 );
SELECT @tmpSql = @tmpSql + ' ISNULL(SUM( ' + PayType + ' ), 0) AS ''' + PayType + ''' , ' + CHAR ( 10 )
FROM ( SELECT DISTINCT PayType FROM Inpours ) T
SET @tmpSql = LEFT ( @tmpSql , LEN ( @tmpSql ) - 2 ) + ' FROM ( ' + CHAR ( 10 );
SET @cmdText = @tmpSql + @cmdText + ' ) T GROUP BY CreateTime ' ;
PRINT @cmdText
EXECUTE ( @cmdText );
下面是通过PIVOT来进行行转列的用法,大家可以对比一下,确实要简单、更具可读性(呵呵,习惯的前提下)
![](https://i-blog.csdnimg.cn/blog_migrate/cdec0645add3fc3c328197dda5c76203.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/81178cc93a2a3bb5048d90d76e7ec935.gif)
CreateTime, [ 支付宝 ] , [ 手机短信 ] ,
[ 工商银行卡 ] , [ 建设银行卡 ]
FROM
(
SELECT CONVERT ( VARCHAR ( 10 ), CreateTime, 120 ) AS CreateTime,PayType, Money
FROM Inpours
) P
PIVOT (
SUM ( Money )
FOR PayType IN
( [ 支付宝 ] , [ 手机短信 ] , [ 工商银行卡 ] , [ 建设银行卡 ] )
) AS T
ORDER BY CreateTime
有时可能会出现这样的错误:
消息 325,级别 15,状态 1,第 9 行
'PIVOT' 附近有语法错误。您可能需要将当前数据库的兼容级别设置为更高的值,以启用此功能。有关存储过程 sp_dbcmptlevel 的信息,请参见帮助。
这个是因为:对升级到 SQL Server 2005 或更高版本的数据库使用 PIVOT 和 UNPIVOT 时,必须将数据库的兼容级别设置为 90 或更高。有关如何设置数据库兼容级别的信息,请参阅 sp_dbcmptlevel (Transact-SQL)。 例如,只需在执行上面脚本前加上 EXEC sp_dbcmptlevel Test, 90; 就OK了, Test 是所在数据库的名称。
下面我们来看看列转行,主要是通过UNION ALL ,MAX来实现。假如有下面这么一个表
![](https://i-blog.csdnimg.cn/blog_migrate/cdec0645add3fc3c328197dda5c76203.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/81178cc93a2a3bb5048d90d76e7ec935.gif)
(
ProgrectName NVARCHAR ( 20 ), -- 工程名称
OverseaSupply INT , -- 海外供应商供给数量
NativeSupply INT , -- 国内供应商供给数量
SouthSupply INT , -- 南方供应商供给数量
NorthSupply INT -- 北方供应商供给数量
)
INSERT INTO ProgrectDetail
SELECT ' A ' , 100 , 200 , 50 , 50
UNION ALL
SELECT ' B ' , 200 , 300 , 150 , 150
UNION ALL
SELECT ' C ' , 159 , 400 , 20 , 320
UNION ALL
SELECT ' D ' , 250 , 30 , 15 , 15
我们可以通过下面的脚本来实现,查询结果如下图所示
![](https://i-blog.csdnimg.cn/blog_migrate/cdec0645add3fc3c328197dda5c76203.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/81178cc93a2a3bb5048d90d76e7ec935.gif)
MAX (OverseaSupply) AS ' SupplyNum '
FROM ProgrectDetail
GROUP BY ProgrectName
UNION ALL
SELECT ProgrectName, ' NativeSupply ' AS Supplier,
MAX (NativeSupply) AS ' SupplyNum '
FROM ProgrectDetail
GROUP BY ProgrectName
UNION ALL
SELECT ProgrectName, ' SouthSupply ' AS Supplier,
MAX (SouthSupply) AS ' SupplyNum '
FROM ProgrectDetail
GROUP BY ProgrectName
UNION ALL
SELECT ProgrectName, ' NorthSupply ' AS Supplier,
MAX (NorthSupply) AS ' SupplyNum '
FROM ProgrectDetail
GROUP BY ProgrectName
用UNPIVOT 实现如下:
![](https://i-blog.csdnimg.cn/blog_migrate/cdec0645add3fc3c328197dda5c76203.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/81178cc93a2a3bb5048d90d76e7ec935.gif)
FROM
(
SELECT ProgrectName, OverseaSupply, NativeSupply,
SouthSupply, NorthSupply
FROM ProgrectDetail
)T
UNPIVOT
(
SupplyNum FOR Supplier IN
(OverseaSupply, NativeSupply, SouthSupply, NorthSupply )
) P