列行转换
表Shape
shapeCode shapeName
11 温度
12 青果
13 篮子
14 空
15 托盘
16 PC
转换成临时表 Shape1
shapeCode 11 12 13 14 15 16
shapeName 温度 青果 篮子 空 托盘 PC
[解决办法]
--SQL2000
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case when shapeCode='+rtrim(shapeCode)+' then shapeName end) as ['+rtrim(shapeCode)+']'
from Shape
select @sql='select ''shapeName'' as shapeCode'+@sql+' from shape'
--如果要生成臨時表
--select @sql='select ''shapeName'' as shapeCode'+@sql+' into shape1 from shape'
exec(@sql)
[解决办法]
create table tb(shapeCode int,shapeName varchar(10))
insert into tb values(11, '温度')
insert into tb values(12, '青果')
insert into tb values(13, '篮子')
insert into tb values(14, '空')
insert into tb values(15, '托盘')
insert into tb values(16, 'PC')
go
declare @sql varchar(8000)
set @sql = 'select shapeCode'
select @sql = @sql + ' , max(case tmpcode when ''' + cast(tmpcode as varchar) + ''' then shapename end) [' + cast(tmpcode as varchar) + ']'
from (select distinct tmpcode from (select 'shapeName' as shapeCode , shapeCode tmpcode , shapename from tb) t) as a
set @sql = @sql + ' from (select ''shapeName'' as shapeCode , shapeCode tmpcode , shapename from tb) t group by shapeCode'
exec(@sql)
drop table tb
/*
shapeCode 11 12 13 14 15 16
--------- ---------- ---------- ---------- ---------- ---------- ----------
shapeName 温度 青果 篮子 空 托盘 PC
*/
