/*
作者: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