大侠们帮帮我， 在SQL server中行列转换问题
跪求各位大侠，小弟有如下问题需要帮忙
我现在原视图如下（试图中的数据是从一张表中动态计算出来的）；
SPBM YGTJS SJTJS BFL
技术部 75 51 68%
资材部 29 29 100%
生产部 1069 821 77%
我需要转换成如下表
SPBM 技术部 资材部 生产部
YGTJS 75 29 1069
SJTJS 51 29 821
BFL 68% 100% 77%
[解决办法]
Declare @SPJS Varchar(8000), @SPBM Varchar(8000)
Select @SPJS = ' ', @SPBM = ' '
Select @SPJS = @SPJS + ' Union All Select SPBM, ' + Cast(ColID As Varchar) + ' As ColID, ' ' ' + Name + ' ' ' As SPJS, Cast( ' + Name + ' As Varchar) As [ALLSPJS] From TEST '
From SysColumns Where ID = OBJECT_ID( 'TEST ') And Name != 'SPBM ' Order By ColID
Select @SPJS = Stuff(@SPJS, 1, 10, ' ')
Select @SPBM = @SPBM + ', Max(Case SPBM When ' ' ' + SPBM + ' ' ' Then ALLSPJS Else ' ' ' ' End) As ' + SPBM From TEST Group By SPBM
Print @SPJS
Print @SPBM
EXEC( ' Select SPJS ' + @SPBM + ' From ( ' + @SPJS + ' ) A Group By SPJS Order By Min(ColID) ')
[解决办法]
create table #t
(SPBM varchar(100) , YGTJS varchar(100), SJTJS varchar(100), BFL varchar(100))
insert into #t
select '技術部 ', '75 ' , '51 ' , '68% ' union all
select '資材部 ', '29 ' , '29 ' , '100% ' union all
select '生產部 ', '1069 ' , '821 ' , '77% '
select * into #t1 from
(
select SPBM, 'YGTJS 'as name,YGTJS from #t
union all
select SPBM, 'SJTJS ',SJTJS from #t
union all
select SPBM, 'BFL ',BFL from #t
)a
select name,min(case when SPBM= '技術部 ' then YGTJS end) as '技術部 ',
min(case when SPBM= '資材部 ' then YGTJS end) as '資材部 ',
min(case when SPBM= '生產部 ' then YGTJS end) as '生產部 '
from #t1
group by name
name 技術部 資材部 生產部
----- ---------------------------------------------------------------- ---------------------------------------------------------------- ----------------------------------------------------------------
YGTJS 75 29 1069
SJTJS 51 29 821
BFL 68% 100% 77%
(3 row(s) affected)
Warning: Null value is eliminated by an aggregate or other SET operation.
[解决办法]
create table tSPBM(SPBM varchar(100),YGTJS int,SJTJS int,BFL varchar(100))
insert into tSPBM
select '技术部 ',75,51, '68% '
union all
select '资材部 ',29,29, '100% '
union all
select '生产部 ',1069,821, '77% '
declare @osql varchar(8000)
declare @tsql varchar(8000)
set @tsql= ' '
select @tsql= 'select spbm= ' 'YGTJS ' ', '
select @tsql=@tsql+ ' '+SPBM+ '=(select ltrim(YGTJS) from tSPBM where SPBM= ' ' '+SPBM+ ' ' '), '
from tSPBM
select @tsql=left(@tsql,len(@tsql)-1)
select @tsql=@tsql + ' union all '
select @osql= ' '
select @osql=@osql+replace(@tsql, 'YGTJS ', ' '+[name]+ ' ')
from syscolumns where object_id( 'tSPBM ')=id and [name]!= 'SPBM '
select @osql=left(@osql,len(@osql)-10)
execute(@osql)
