大侠们帮帮我, 在SQL server中行列转换问题

时间:2021-11-22 05:01:23
跪求各位大侠,小弟有如下问题需要帮忙
我现在原视图如下(试图中的数据是从一张表中动态计算出来的);
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


是撒,命苦啊。 :)

#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