我现在原视图如下(试图中的数据是从一张表中动态计算出来的);
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)')
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%
*/
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%
*/
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.
(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)
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
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%
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
#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)')
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%
*/
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%
*/
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.
(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)
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
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%
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
是撒,命苦啊。 :)