行列转换-->动态SQL语句例子
1.例子
1 create table cjb(姓名 char (10),课程名 char (10),成绩 [decimal](12, 2)) 2 insert into cjb 3 select '张','数据库', 78 union 4 select '张','信息管理',80 union 5 select '张','专业英语',89 union 6 select '李','数据库' ,90 union 7 select '李','信息管理',67 union 8 select '李','专业英语',56 9 10 --方法一(SQL SERVER2005以上) 11 declare @groupField varchar(1000) 12 select @groupField=isnull(@groupField,'')+case when isnull(@groupField,'')='' then '' else ',' end+QUOTENAME(课程名) 13 from (select distinct rtrim(课程名) as 课程名 from cjb)t 14 declare @sql nvarchar(4000) 15 set @sql=N'select * 16 from 17 (select 姓名, rtrim(课程名) as 课程名,sum(成绩) as 成绩 18 from cjb 19 group by 姓名,rtrim(课程名) 20 ) as x 21 pivot (sum(成绩)for 课程名 in ('+@groupField+')) as pvt 22 order by 姓名' 23 EXEC (@sql) 24 25 26 --方法二(一般用在SQL SERVER2000) 27 declare @sql varchar(8000) 28 set @sql='' 29 select @sql=@sql + ',['+rtrim(课程名)+']=max(case rtrim(课程名) when '''+rtrim(课程名)+''' then 成绩 end)' 30 from cjb group by rtrim(课程名) 31 exec('select 姓名'+@sql+' from cjb group by 姓名') 32 33 drop table cjb
2.例子
1 create table tb1(id int,typeid int,value numeric(10,1)) 2 Insert into tb1 3 select '1','1','2.5' 4 union all select '1','3','3' 5 union all select '1','2','6' 6 union all select '2','3','6' 7 union all select '3','2','1.5' 8 9 create table tb2 (typeid int,type varchar(1)) 10 Insert into tb2 11 select '1','A' 12 union all select '2','B' 13 union all select '3','C' 14 15 select * from tb1 16 select * from tb2 17 18 19 declare @sql varchar(8000) 20 set @sql='' 21 select @sql=@sql+', ['+max(b.type)+']=sum(case b.type when '''+max(b.type)+''' then a.value else 0 end)' 22 from tb1 a left join tb2 b on a.typeid=b.typeid group by b.typeid 23 24 print @sql 25 exec('select a.id'+@sql+' from tb1 a left join tb2 b on a.typeid=b.typeid group by a.id order by a.id') 26 27 --结果 28 id A B C 29 ---------------------- 30 1 2.5 6.0 3.0 31 2 .0 .0 6.0 32 3 .0 1.5 .0
3.例子
1 create table tb(名称 varchar(10),数量 numeric(10),类型 varchar(5)) 2 Insert into tb 3 select 'L001','1','A' 4 union all select 'L001','2','B' 5 union all select 'L002','5','C' 6 union all select 'L003','6','D' 7 union all select 'L004','9','A' 8 union all select 'L004','5','D' 9 10 select * from tb 11 12 declare @sql varchar(1000) 13 set @sql='' 14 select @sql=@sql+',['+max(类型)+']=sum(case类型 when '''+max(类型)+''' then 数量 else 0 end)' 15 from tb group by类型 16 print @sql 17 18 exec('select名称'+@sql+' from tb group by 名称') 19 --结果 20 名称 A B C D 21 --------------------------------------- 22 L001 1 2 0 0 23 L002 0 0 5 0 24 L003 0 0 0 6 25 L004 9 0 0 5
4.例子
1 create table AccountMessage(FFundCode varchar(6),FAccName varchar(8),FAccNum int) 2 Insert into AccountMessage 3 select '000001','北京存款','1' 4 union all select '000001','上海存款','2' 5 union all select '000001','深圳存款','3' 6 union all select '000002','北京存款','1' 7 union all select '000002','上海存款','2' 8 union all select '000002','天津存款','3' 9 union all select '000003','上海存款','1' 10 union all select '000003','福州存款','2' 11 12 select * from AccountMessage 13 14 create table AccountBalance(FDate datetime,FFundCode varchar(6),FAccNum int , FBal numeric(12,2)) 15 Insert into AccountBalance 16 select '2004-07-28','000001','1','1000.00' 17 union all select '2004-07-28','000001','2','1000.00' 18 union all select '2004-07-28','000001','3','1120.00' 19 union all select '2004-07-28','000002','1','2000.00' 20 union all select '2004-07-28','000002','2','1000.00' 21 union all select '2004-07-28','000002','3','1000.00' 22 union all select '2004-07-28','000003','1','2000.00' 23 union all select '2004-07-28','000003','2','1000.00' 24 union all select '2004-07-28','000003','2','1000.00' 25 26 select * from AccountBalance 27 28 declare @sql varchar(8000) 29 set @sql='' 30 select @sql=@sql+',['+a.FAccName+']=SUM(CASE a.FAccName WHEN '''+a.FAccName+''' THEN b.FBal ELSE 0 END)' 31 from AccountMessage a 32 left join AccountBalance b on a.FFundCode=b.FFundCode 33 group by a.FAccName 34 print @sql 35 36 exec('SELECT基金代码=a.FFundCode'+@sql+' FROM AccountMessage a LEFT JOIN AccountBalance b ON a.FFundCode=b.FFundCode AND a.FAccNum=b.FAccNum GROUP BY a.FFundCode') 37 38 --结果 39 基金代码 上海存款 天津存款 北京存款 深圳存款 福州存款 40 000001 1000.00.00 1000.001120.00.00 41 000002 1000.001000.002000.00.00 .00 42 000003 2000.00.00 .00 .00 1000.00
5.实例应用：金成色行列转换
1 declare @sql varchar(1000) 2 set @sql='' 3 select @sql=@sql+',['+max(tbProjectItem.name)+']=sum(case when tbGoldPrice.gold_color='''+max(tbProjectItem.item_no)+''' then tbGoldPrice.unit_price else 0 end)' 4 from tbProjectItem 5 left join tbGoldPrice on tbProjectItem.item_no=tbGoldPrice.gold_color 6 where tbProjectItem.project_no='goldcolor' 7 group by name 8 print @sql 9 10 exec('select tbGoldPrice.set_day'+@sql+' from tbProjectItem left join tbGoldPrice on tbProjectItem.item_no=tbGoldPrice.gold_color where tbProjectItem.project_no=''goldcolor'' group by tbGoldPrice.set_day')
