行列转换-->动态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
View Code

 

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
View Code

 

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
View Code

 

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
View Code

 

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')
View Code

 

 

 

 

 

 

 

posted @ 2014-02-28 10:06  Spacecup  阅读(287)  评论(0)  编辑  收藏  举报