行列转换-->动态SQL语句例子

1.例子

create table cjb(姓名 char (10),课程名 char (10),成绩 [decimal](12, 2))
insert into cjb 
select '张','数据库',  78 union
select '张','信息管理',80 union
select '张','专业英语',89 union
select '李','数据库'  ,90 union
select '李','信息管理',67 union
select '李','专业英语',56

--方法一(SQL SERVER2005以上)
declare @groupField varchar(1000)
select @groupField=isnull(@groupField,'')+case when isnull(@groupField,'')='' then '' else ',' end+QUOTENAME(课程名) 
from (select distinct rtrim(课程名) as 课程名 from cjb)t
declare @sql nvarchar(4000)
set @sql=N'select *
	from 
		(select 姓名, rtrim(课程名) as 课程名,sum(成绩) as 成绩   
		from cjb 
		group by 姓名,rtrim(课程名)
		) as x
	pivot (sum(成绩)for 课程名 in ('+@groupField+')) as pvt
	order by 姓名'
EXEC (@sql)


--方法二(一般用在SQL SERVER2000)
declare @sql varchar(8000)
set @sql=''
select @sql=@sql + ',['+rtrim(课程名)+']=max(case rtrim(课程名) when '''+rtrim(课程名)+''' then 成绩 end)'
from cjb group by rtrim(课程名)
exec('select 姓名'+@sql+' from cjb group by 姓名')

drop table cjb

 

 

2.例子

create table tb1(id int,typeid int,value numeric(10,1))

Insert into tb1

select '1','1','2.5'

union all select '1','3','3'

union all select '1','2','6'

union all select '2','3','6'

union all select '3','2','1.5'

 

create table tb2 (typeid int,type varchar(1))

Insert into tb2

select '1','A'

union all select '2','B'

union all select '3','C'

 

select * from tb1

select * from tb2

 

 

declare @sql varchar(8000)

set @sql=''

select @sql=@sql+', ['+max(b.type)+']=sum(case b.type when '''+max(b.type)+''' then a.value else 0 end)'

from tb1 a left join tb2 b on a.typeid=b.typeid group by b.typeid

 

print @sql

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

 

--结果

id A B  C

----------------------

1    2.5 6.0 3.0

2    .0   .0   6.0

3    .0   1.5 .0

 

 

3.例子

create table tb(名称 varchar(10),数量 numeric(10),类型 varchar(5))

Insert into tb

select 'L001','1','A'

union all select 'L001','2','B'

union all select 'L002','5','C'

union all select 'L003','6','D'

union all select 'L004','9','A'

union all select 'L004','5','D'

 

select * from tb

 

declare @sql varchar(1000)

set @sql=''

select @sql=@sql+',['+max(类型)+']=sum(case类型 when '''+max(类型)+''' then 数量 else 0 end)'

from tb group by类型

print @sql

 

exec('select名称'+@sql+' from tb  group by 名称')

--结果

名称     A       B        C       D

---------------------------------------

L001     1    2    0    0

L002     0    0    5    0

L003     0    0    0    6

L004     9    0    0    5

 

 

4.例子

 

create table AccountMessage(FFundCode varchar(6),FAccName varchar(8),FAccNum int)

Insert into AccountMessage

select '000001','北京存款','1'

union all select '000001','上海存款','2'

union all select '000001','深圳存款','3'

union all select '000002','北京存款','1'

union all select '000002','上海存款','2'

union all select '000002','天津存款','3'

union all select '000003','上海存款','1'

union all select '000003','福州存款','2'

 

select * from AccountMessage

 

create table AccountBalance(FDate datetime,FFundCode varchar(6),FAccNum int , FBal numeric(12,2))

Insert into AccountBalance

select '2004-07-28','000001','1','1000.00'

union all select '2004-07-28','000001','2','1000.00'

union all select '2004-07-28','000001','3','1120.00'

union all select '2004-07-28','000002','1','2000.00'

union all select '2004-07-28','000002','2','1000.00'

union all select '2004-07-28','000002','3','1000.00'

union all select '2004-07-28','000003','1','2000.00'

union all select '2004-07-28','000003','2','1000.00'

union all select '2004-07-28','000003','2','1000.00'

 

select * from AccountBalance

 

declare @sql varchar(8000)

set @sql=''

select @sql=@sql+',['+a.FAccName+']=SUM(CASE a.FAccName WHEN '''+a.FAccName+''' THEN b.FBal ELSE 0 END)'

from AccountMessage a

left join AccountBalance b on a.FFundCode=b.FFundCode

group by a.FAccName

print @sql

 

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

 

--结果

基金代码    上海存款    天津存款    北京存款    深圳存款    福州存款

000001 1000.00.00 1000.001120.00.00

000002 1000.001000.002000.00.00 .00

000003 2000.00.00 .00 .00 1000.00

 

 

5.实例应用：金成色行列转换

declare @sql varchar(1000)

set @sql=''

select @sql=@sql+',['+max(tbProjectItem.name)+']=sum(case when tbGoldPrice.gold_color='''+max(tbProjectItem.item_no)+''' then tbGoldPrice.unit_price else 0 end)'

from tbProjectItem

left join tbGoldPrice on tbProjectItem.item_no=tbGoldPrice.gold_color

where tbProjectItem.project_no='goldcolor'

group by name

print @sql

 

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