在2000或2005中对一个多行多列的表进行转置

/*
作者:qianjin036a
日期:2011-07-03

MSSQL中表的转置程序,希望将

项目         指标1        指标2        指标3
---------- ---------- ---------- ----------
项目名1       aa         bb         cc
项目名2       dd         ee         ff
项目名3       gg         hh         ii

转置为

指标项    项目名1       项目名2       项目名3
----- ---------- ---------- ----------
指标1   aa         dd         gg
指标2   bb         ee         hh
指标3   cc         ff         ii

*/

--创建并生成表
create table tb(项目 varchar(10),指标1 varchar(10),指标2 varchar(10),指标3 varchar(10))
insert tb
select '项目名1','aa','bb','cc' union all
select '项目名2','dd','ee','ff' union all
select '项目名3','gg','hh','ii'

go
--将原表结构化为3列(项目,指标项,指标值),转入新表(如用临时表,必须用全局临时表,否则不能在动态语句中调用),以便进行转置,必须的!
select * into tb1 from(
select 项目,'指标1' 指标项,指标1 指标值 from tb
union all
select 项目,'指标2',指标2 from tb
union all
select 项目,'指标3',指标3 from tb
--如果还有列,继续 union 下去
)t

--2005 用 pivot 行转列

declare @s nvarchar(4000)
select @s=isnull(@s+',','')+'['+ 项目 +']' from(
select distinct [项目] from tb1
)t
exec('select [指标项],'+@s+' from tb1 pivot (max([指标值]) for 项目 in('+@s+'))b')

/*
--这是用2005得到的查询结果,与原表为转置结构
指标项    项目名1       项目名2       项目名3
----- ---------- ---------- ----------
指标1   aa         dd         gg
指标2   bb         ee         hh
指标3   cc         ff         ii

(3 行受影响)
*/

--2000 用动态行转列

set @s='
Select @s=@s+','+quotename(项目)+'=max(case when 项目='+quotename(项目,'')+' then 指标值 else '' end)'
from tb1 group by 项目
exec('select 指标项'+@s+' from tb1 group by 指标项')

/*
--这是用2000得到的查询结果,与原表亦为转置结构
指标项    项目名1       项目名2       项目名3
----- ---------- ---------- ----------
指标1   aa         dd         gg
指标2   bb         ee         hh
指标3   cc         ff         ii

(3 行受影响)
*/

go
drop table tb,tb1

/*
注:
原表中不能存在名称相同的项目,否则得不到正确的转置结果,这是由于在转置程序中用了max,则取数据较大的,而不会自动增加一列:
如原表增加:
union all select '项目名2','kk','00','mm'
则转置结果为

指标项   项目名1       项目名2       项目名3
----- ---------- ---------- ----------
指标1   aa         kk         gg
指标2   bb         ee         hh
指标3   cc         mm         ii

如果一定要转置,可以考虑在生成 tb1 的时候,对相同项目名进行处理,得到不同的项目名,以便得到不同的转置列.
*/

--有一表,不知其有多少列多少行,但第一列肯定有,且第一列的列名已知,要在2000中以第一行第一列为基点进行转置
create table tb(项目 varchar(20),指标1 varchar(20),指标2 varchar(20),指标3 varchar(20))
insert tb
select '项目名1','aa','bb','cc' union all
select '项目名2','dd','ee','ff' union all
select '项目名3','gg','hh','ii'
go
--先将表转为以第一列为选择列的三列结构
create table #t(项目 varchar(20),指标项 varchar(20),指标值 varchar(20))
declare @sql nvarchar(4000)
select @sql=isnull(@sql+' union all ','')+'select 项目,'''+name+''' 指标项,'+name+' 指标值 from tb '
from syscolumns where id=object_id('tb') and name<>'项目'
insert into #t exec(@sql)
--再将临时表内容动态行转列
set @sql=''
Select @sql=@sql+','+quotename(项目)+'=max(case when 项目='''+项目+''' then 指标值 else '''' end)'
from #t group by 项目


exec('select 指标项 '+@sql+' from #t group by 指标项')
/*
指标项                  项目名1                 项目名2                 项目名3
-------------------- -------------------- -------------------- --------------------
指标1                  aa                   dd                   gg
指标2                  bb                   ee                   hh
指标3                  cc                   ff                   ii


(3 行受影响)


*/


go
drop table tb,#t