Table:TB
AtDate Dept1 Dept2 Dept3
-------------------------------
2008/8/1 1 2 3
2008/8/2 4 5 6Result:
Dept 2008/8/1 2008/8/2
-------------------------------
Dept1 1 4
Dept2 2 5
Dept3 3 6 --Dept不止3个,AtDate也不止2个。
AtDate Dept1 Dept2 Dept3
-------------------------------
2008/8/1 1 2 3
2008/8/2 4 5 6Result:
Dept 2008/8/1 2008/8/2
-------------------------------
Dept1 1 4
Dept2 2 5
Dept3 3 6 --Dept不止3个,AtDate也不止2个。
http://blog.csdn.net/roy_88/archive/2008/07/26/2715856.aspx
go
--> --> if not object_id('TB') is null
drop table TB
Go
Create table TB([AtDate] Datetime,[Dept1] int,[Dept2] int,[Dept3] int)
Insert TB
select '2008/8/1',1,2,3 union all
select '2008/8/2',4,5,6
Go
declare @s nvarchar(4000),@s2 nvarchar(4000),@s3 nvarchar(4000),@s4 nvarchar(4000)
select
@s=isnull(@s+',','declare ')+'@'+rtrim(Colid)+' nvarchar(4000)',
@s2=isnull(@s2+',','select ')+'@'+rtrim(Colid)+'='''+case when @s2 is not null then 'union all select' else ' select ' end+' [Dept]='''+quotename(Name,'''')+'''''',
@s3=isnull(@s3,'')+'select @'+rtrim(Colid)+'=@'+rtrim(Colid)+'+'',''+quotename(convert(varchar(10),[AtDate],120))+''=''+quotename('+quotename(Name)+','''''''') from TB ',
@s4=isnull(@s4+'+','')+'@'+rtrim(Colid)
from
syscolumns
where
id=object_id('TB') and Name not in('AtDate')
--print @s+' '+@s2+' '+@s3+' exec('+@s4+')' 显示执行语句
exec(@s+' '+@s2+' '+@s3+' exec('+@s4+')')
Dept 2008-08-01 2008-08-02
----- ---------- ----------
Dept1 1 4
Dept2 2 5
Dept3 3 6(3 個資料列受到影響)