行转列,是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 EndCreate 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 OrangeFrom SalesGroup By Name--pivot方案 sql 2005及以上版本
Select * From SalesPIVOT( 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 SalesPIVOT( SUM(Quantity) For Product in('+@sql+')) as pvt'exec(@sql)
现在随意新增商品都可以自动生成我们想要的结果了。