大侠们帮帮我， 在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%

19 个解决方案

#1

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)')

#2

--創建測試環境
Create Table TEST
(SPBM Nvarchar(20),
 YGTJS Int,
 SJTJS Int,
 BFL Nvarchar(20))
--插入數據
Insert TEST Select N'技術部',     75,        51,      '68%'
Union All Select N'資材部',     29,        29,      '100%'
Union All Select N'生產部',     1069,    821,     '77%'
GO
--測試
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)')
GO
--刪除測試環境
Drop Table TEST
--結果
/*
SPJS 生產部 技術部 資材部
YGTJS 1069 75 29
SJTJS 821 51 29
BFL 77% 68% 100%
*/

#3

--创建测试环境
Create Table TEST
(SPBM Nvarchar(20),
 YGTJS Int,
 SJTJS Int,
 BFL Nvarchar(20))
--插入数据
Insert TEST Select N'技术部',     75,        51,      '68%'
Union All Select N'资材部',     29,        29,      '100%'
Union All Select N'生产部',     1069,    821,     '77%'
GO
--测试
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)')
GO
--删除测试环境
Drop Table TEST
--结果
/*
SPJS 生产部 技术部 资材部
YGTJS 1069 75 29
SJTJS 821 51 29
BFL 77% 68% 100%
*/

#4

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.

#5

怎麼都變型了

慢了！

#6

要用動態語句來做吧，樓主的數據應該不是那麼固定的。

#7

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
order by name desc

--動態
declare @str varchar(1000)
set @str='select name '
select @str=@str+',min(case when spbm='''+spbm+''' then ygtjs end)as '''+spbm+''' '
from #t1 group by spbm
select @str=@str+' from #t1 group by name'
exec(@str)


name  生產部                                                                                                  技術部                                                                                                  資材部                             
BFL   77%                                                                                                  68%                                                                                                  100%
SJTJS 821                                                                                                  51                                                                                                   29
YGTJS 1069                                                                                                 75                                                                                                   29

Warning: Null value is eliminated by an aggregate or other SET operation.

#8

呵呵，我也來個動態的

#9

我說你的語句怎麼簡單不少呢，你創建了一個表來中轉，怪不得。：)

#10

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)

#11

SPJS 生产部 技术部 资材部
BFL 77% 68% 100%
SJTJS 821 51 29
YGTJS 1069 75 29

#12

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' order by ColID
select @osql=left(@osql,len(@osql)-10)
execute(@osql)



上面没order by ColID
SPJS 生产部 技术部 资材部
YGTJS 1069 75 29
SJTJS 821 51 29
BFL 77% 68% 100%

#13

paoluo(一天到晚游泳的鱼(學習.NET中)) ( ) 信誉：100  2007-09-01 15:22:42  得分: 0  
 
 
   我說你的語句怎麼簡單不少呢，你創建了一個表來中轉，怪不得。：)

  
 
----------------
是啊，這樣是簡單了不少

#14

語句的確是簡單不少。

看樓主的需要了，通常能用語句實現的，就不想再多去建個表了。

另外，全部用動態的話，可以不用自己去查列名了。

#15

学习 学习ing

#16

呵呵 魚兄，你寫的語句好復雜喔，要好好研究下你的代碼,學習

#17

其實不複雜，無非就是將你那段生成臨時表的語句改為了一個子查詢，原理是一樣的。

#18

謝謝指教， 魚兄今天也上班啊，

#19

是撒，命苦啊。 :)

#20

• 锋利的SQL-SQL Server的表旋转（行列转换）
• sql server行列转化和行列置换