MSSQL—行转列

行转列，是SQL中经常会遇到的一个问题，并且分为静态转换和动态转换，所谓静态转换即在转换的行数已知或固定；动态转换则为转换的行数不固定。

转换的方法一般采用case when语句或pivot（MSSQL 2005及以上版本）来实现。

首先来看静态转换：先准备一下测试数据，使用如下SQL语句直接生成：

If Exists (Select * From sysobjects Where
id = OBJECT_ID(‘Sales‘) and OBJECTPROPERTY(id, ‘IsUserTable‘) = 1)
    Begin
        Drop Table Sales
    End

Create Table Sales(
Name nvarchar(10),
Product nvarchar(12),
Quantity int)

Insert Into Sales(Name,Product,Quantity)
Values(‘Leo‘,‘Apple‘,300),
(‘Leo‘,‘Orange‘,23),
(‘Amy‘,‘Apple‘,432),
(‘Amy‘,‘Banana‘,45),
(‘Leo‘,‘Banana‘,74),
(‘Tomy‘,‘Apple‘,57),
(‘Tomy‘,‘Orange‘,92),
(‘Tim‘,‘Apple‘,76),
(‘Tim‘,‘Banana‘,45),
(‘Tim‘,‘Orange‘,159),
(‘Amy‘,‘Orange‘,610),
(‘Alice‘,‘Apple‘,245),
(‘Alice‘,‘Banana‘,342),
(‘Alice‘,‘Orange‘,138)
Go

运行后生成一个Sales表，数据如下：

现在要以商品名称（Product）的记录作为列，按照姓名来统计卖出的数量，如果某人未卖出某商品，则用“NULL”表示，即生成如下的格式：

分别用case when和pivot解决方法如下：

--case when方案
Select Name,
sum(Case When Product=‘Apple‘ Then Quantity Else NULL End) as Apple,
sum(Case When Product=‘Banana‘ Then Quantity Else NULL End) as Banana,
sum(Case When Product=‘Orange‘ Then Quantity Else NULL End) as Orange
From Sales
Group By Name

--pivot方案 sql 2005及以上版本
Select * From Sales
PIVOT
(
    SUM(Quantity) For Product in(Apple,Banana,Orange)
) as pvt

以上是静态方案，如果有一天商品又增加了strawberry，pear等，如果要实现之前的结果，按照上面的方案就又要手动去修改SQL语句，增加两种商品列，有没有一劳永逸的解决方案呢，答案当然有，就是动态SQL，结合上面的SQL语句可以看出，如果在case when中可以先找到所有的商品，然后循环生成case when语句就可以解决了；在pivot解决方案中只要将所有商品记录生成一个以“,”连接的字符串。

具体的实现语句如下：

--case when方案
Declare @sql varchar(8000)
Set @sql=‘Select Name‘
Select @sql=@sql+‘ ,sum(Case When Product=‘‘‘+Product+‘‘‘ Then Quantity Else NULL End) as ‘+Product+‘‘ 
From (Select DISTINCT Product From Sales) As t    --变量遍历赋值
Set @sql=@sql+‘ From Sales Group By Name‘
exec(@sql)

--pivot方案 sql 2005及以上版本
Declare @sql varchar(8000)
Set @sql=(Select DISTINCT ‘,‘+Product From Sales FOR XML PATH(‘‘))
Set @sql=STUFF(@sql,1,1,‘‘)
Set @sql=‘Select * From Sales
PIVOT
(
    SUM(Quantity) For Product in(‘+@sql+‘)
) as pvt‘
exec(@sql)

现在随意新增商品都可以自动生成我们想要的结果了。