行列轉(zhuǎn)換的SQL

時(shí)間：2018-11-20 11:10:01

關(guān)鍵字： sql insert

手機(jī)看文章

掃描二維碼
隨時(shí)隨地手機(jī)看文章

[導(dǎo)讀]/*標(biāo)題：普通行列轉(zhuǎn)換(version 2.0)作者：愛(ài)新覺(jué)羅.毓華時(shí)間：2008-03-09地點(diǎn)：廣東深圳說(shuō)明：普通行列轉(zhuǎn)換(version 1.0)僅針對(duì)sql server 2000提供靜態(tài)和

/*
標(biāo)題：普通行列轉(zhuǎn)換(version 2.0)
作者：愛(ài)新覺(jué)羅.毓華
時(shí)間：2008-03-09
地點(diǎn)：廣東深圳
說(shuō)明：普通行列轉(zhuǎn)換(version 1.0)僅針對(duì)sql server 2000提供靜態(tài)和動(dòng)態(tài)寫(xiě)法，version 2.0增加sql server 2005的有關(guān)寫(xiě)法。

問(wèn)題：假設(shè)有張學(xué)生成績(jī)表(tb)如下:
姓名課程分?jǐn)?shù)
張三語(yǔ)文 74
張三數(shù)學(xué) 83
張三物理 93
李四語(yǔ)文 74
李四數(shù)學(xué) 84
李四物理 94
想變成(得到如下結(jié)果)：
姓名語(yǔ)文數(shù)學(xué) 物理
---- ---- ---- ----
李四 74?? 84?? 94
張三 74?? 83?? 93
-------------------
*/

create table tb(姓名 varchar(10) , 課程 varchar(10) , 分?jǐn)?shù) int)
insert into tb values('張三' , '語(yǔ)文' , 74)
insert into tb values('張三' , '數(shù)學(xué)' , 83)
insert into tb values('張三' , '物理' , 93)
insert into tb values('李四' , '語(yǔ)文' , 74)
insert into tb values('李四' , '數(shù)學(xué)' , 84)
insert into tb values('李四' , '物理' , 94)
go

--SQL SERVER 2000 靜態(tài)SQL,指課程只有語(yǔ)文、數(shù)學(xué)、物理這三門課程。(以下同)
select 姓名 as 姓名 ,
? max(case 課程 when '語(yǔ)文' then 分?jǐn)?shù) else 0 end) 語(yǔ)文,
? max(case 課程 when '數(shù)學(xué)' then 分?jǐn)?shù) else 0 end) 數(shù)學(xué),
? max(case 課程 when '物理' then 分?jǐn)?shù) else 0 end) 物理
from tb
group by 姓名

--SQL SERVER 2000 動(dòng)態(tài)SQL,指課程不止語(yǔ)文、數(shù)學(xué)、物理這三門課程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 課程 when ''' + 課程 + ''' then 分?jǐn)?shù) else 0 end) [' + 課程 + ']'
from (select distinct 課程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql)

--SQL SERVER 2005 靜態(tài)SQL。
select * from (select * from tb) a pivot (max(分?jǐn)?shù)) for 課程 in (語(yǔ)文,數(shù)學(xué),物理)) b

--SQL SERVER 2005 動(dòng)態(tài)SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 課程 from tb group by 課程
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(分?jǐn)?shù)) for 課程 in (' + @sql + ')) b')

---------------------------------

/*
問(wèn)題：在上述結(jié)果的基礎(chǔ)上加平均分，總分，得到如下結(jié)果：
姓名語(yǔ)文數(shù)學(xué) 物理平均分總分
---- ---- ---- ---- ------ ----
李四 74?? 84?? 94?? 84.00? 252
張三 74?? 83?? 93?? 83.33? 250
*/

--SQL SERVER 2000 靜態(tài)SQL。
select 姓名姓名,
? max(case 課程 when '語(yǔ)文' then 分?jǐn)?shù) else 0 end) 語(yǔ)文,
? max(case 課程 when '數(shù)學(xué)' then 分?jǐn)?shù) else 0 end) 數(shù)學(xué),
? max(case 課程 when '物理' then 分?jǐn)?shù) else 0 end) 物理,
? cast(avg(分?jǐn)?shù)*1.0) as decimal(18,2)) 平均分,
? sum(分?jǐn)?shù)) 總分
from tb
group by 姓名

--SQL SERVER 2000 動(dòng)態(tài)SQL。
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 課程 when ''' + 課程 + ''' then 分?jǐn)?shù) else 0 end) [' + 課程 + ']'
from (select distinct 課程 from tb) as a
set @sql = @sql + ' , cast(avg(分?jǐn)?shù)*1.0) as decimal(18,2)) 平均分 , sum(分?jǐn)?shù)) 總分 from tb group by 姓名'
exec(@sql)

--SQL SERVER 2005 靜態(tài)SQL。
select m.* , n.平均分 , n.總分 from
(select * from (select * from tb) a pivot (max(分?jǐn)?shù)) for 課程 in (語(yǔ)文,數(shù)學(xué),物理)) b) m,
(select 姓名 , cast(avg(分?jǐn)?shù)*1.0) as decimal(18,2)) 平均分 , sum(分?jǐn)?shù)) 總分 from tb group by 姓名) n
where m.姓名 = n.姓名

--SQL SERVER 2005 動(dòng)態(tài)SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 課程 from tb group by 課程
exec ('select m.* , n.平均分 , n.總分 from
(select * from (select * from tb) a pivot (max(分?jǐn)?shù)) for 課程 in (' + @sql + ')) b) m ,
(select 姓名 , cast(avg(分?jǐn)?shù)*1.0) as decimal(18,2)) 平均分 , sum(分?jǐn)?shù)) 總分 from tb group by 姓名) n
where m.姓名 = n.姓名')

drop table tb???

------------------
------------------

/*
問(wèn)題：如果上述兩表互相換一下：即表結(jié)構(gòu)和數(shù)據(jù)為：
姓名語(yǔ)文數(shù)學(xué) 物理
張三 74　　83　　93
李四 74　　84　　94
想變成(得到如下結(jié)果)：
姓名課程分?jǐn)?shù)
---- ---- ----
李四語(yǔ)文 74
李四數(shù)學(xué) 84
李四物理 94
張三語(yǔ)文 74
張三數(shù)學(xué) 83
張三物理 93
--------------
*/

create table tb(姓名 varchar(10) , 語(yǔ)文 int , 數(shù)學(xué) int , 物理 int)
insert into tb values('張三',74,83,93)
insert into tb values('李四',74,84,94)
go

--SQL SERVER 2000 靜態(tài)SQL。
select * from
(
select 姓名 , 課程 = '語(yǔ)文' , 分?jǐn)?shù) = 語(yǔ)文 from tb
union all
select 姓名 , 課程 = '數(shù)學(xué)' , 分?jǐn)?shù) = 數(shù)學(xué) from tb
union all
select 姓名 , 課程 = '物理' , 分?jǐn)?shù) = 物理 from tb
) t
order by 姓名 , case 課程 when '語(yǔ)文' then 1 when '數(shù)學(xué)' then 2 when '物理' then 3 end

--SQL SERVER 2000 動(dòng)態(tài)SQL。
--調(diào)用系統(tǒng)表動(dòng)態(tài)生態(tài)。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [課程] = ' + quotename(Name , '''') + ' , [分?jǐn)?shù)] = ' + quotename(Name) + ' from tb'
from syscolumns
where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名為姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')

--SQL SERVER 2005 動(dòng)態(tài)SQL。
select 姓名 , 課程 , 分?jǐn)?shù) from tb unpivot (分?jǐn)?shù) for 課程 in([語(yǔ)文] , [數(shù)學(xué)] , [物理])) t

--SQL SERVER 2005 動(dòng)態(tài)SQL，同SQL SERVER 2000 動(dòng)態(tài)SQL。

--------------------
/*
問(wèn)題：在上述的結(jié)果上加個(gè)平均分，總分，得到如下結(jié)果：
姓名課程?? 分?jǐn)?shù)
---- ------ ------
李四語(yǔ)文?? 74.00
李四數(shù)學(xué)?? 84.00
李四物理?? 94.00
李四平均分 84.00
李四總分?? 252.00
張三語(yǔ)文?? 74.00
張三數(shù)學(xué)?? 83.00
張三物理?? 93.00
張三平均分 83.33
張三總分?? 250.00
------------------
*/

select * from
(
select 姓名 as 姓名 , 課程 = '語(yǔ)文' , 分?jǐn)?shù) = 語(yǔ)文 from tb
union all
select 姓名 as 姓名 , 課程 = '數(shù)學(xué)' , 分?jǐn)?shù) = 數(shù)學(xué) from tb
union all
select 姓名 as 姓名 , 課程 = '物理' , 分?jǐn)?shù) = 物理 from tb
union all
select 姓名 as 姓名 , 課程 = '平均分' , 分?jǐn)?shù) = cast((語(yǔ)文 + 數(shù)學(xué) + 物理)*1.0/3 as decimal(18,2)) from tb
union all
select 姓名 as 姓名 , 課程 = '總分' , 分?jǐn)?shù) = 語(yǔ)文 + 數(shù)學(xué) + 物理 from tb
) t
order by 姓名 , case 課程 when '語(yǔ)文' then 1 when '數(shù)學(xué)' then 2 when '物理' then 3 when '平均分' then 4 when '總分' then 5 end

drop table tb

================================================================

create table A(id char(3), num1 int, num2 int, num3 int, num4 int)
insert A select '001', 80, 90, 50, 60
insert A select '002', 84, 70, 60, 82
go
--SQL2005實(shí)現(xiàn)方法:
select * from A
unpivot
(num for col in([num1],[num2],[num3],[num4]))T2)tmp

--SQL2000實(shí)現(xiàn):
---調(diào)用系統(tǒng)表動(dòng)態(tài)生態(tài)
declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+' select ID,[num]='+quotename(Name,'''')+',Qty='+quotename(Name)+' from A'
from syscolumns
where Name!=N'ID' and ID=object_id('A')--表名A，不包含列名為ID的其它列
order by colid asc
exec(@s+' order by ID asc,[num] asc')

--生成的靜態(tài)語(yǔ)句
select ID,[num]='num1',Qty=[num1] from A union all
select ID,[num]='num2',Qty=[num2] from A union all
select ID,[num]='num3',Qty=[num3] from A union all
select ID,[num]='num4',Qty=[num4] from A
order by ID asc,[num] asc

/*

ID num Qty
---- ---- -----------
001 num1 80
001 num2 90
001 num3 50
001 num4 60
002 num1 84
002 num2 70
002 num3 60
002 num4 82
------------------------------
*/

--動(dòng)態(tài)方法:
declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+' select ID,[num]='+quotename(Name)+' from A'
from syscolumns
where Name!=N'ID' and ID=object_id('A')
order by colid asc
exec(@s+' order by ID asc')

--生成的語(yǔ)句如下:

select ID,[num]=[num1] from A union all
select ID,[num]=[num2] from A union all
select ID,[num]=[num3] from A union all
select ID,[num]=[num4] from A
order by ID asc,[num] asc

/*
ID num
---- -----------
001 80
001 90
001 50
001 60
002 82
002 60
002 70
002 84
*/

---drop table A

====================================================================

/*
將表數(shù)據(jù)旋轉(zhuǎn)90度(2007-11-19于海南三亞)

將下表數(shù)據(jù)：
A??????????????????? b?????????? c?????????? d?????????? e??????????
-------------------- ----------- ----------- ----------- -----------
x??????????????????? 1?????????? 2?????????? 3?????????? 4
y??????????????????? 5?????????? 6?????????? 7?????????? 8
z??????????????????? 9?????????? 10????????? 11????????? 12

轉(zhuǎn)化成如下結(jié)果：
a??????????????????? x????????? y????????? z?????????
-------------------- ---------- ---------- ----------
b??????????????????? 1????????? 5????????? 9
c??????????????????? 2????????? 6????????? 10
d??????????????????? 3????????? 7????????? 11
e??????????????????? 4????????? 8????????? 12

*/

--生成測(cè)試數(shù)據(jù)
create table test1(A varchar(20),b int,c int,d int,e int)
insert into test1 select 'x',1,2 ,3 ,4
insert into test1 select 'y',5,6 ,7 ,8
insert into test1 select 'z',9,10,11,12
go

--生成中間數(shù)據(jù)表
declare @s varchar(8000)
set @s = 'create table test2(a varchar(20)'
select @s = @s + ',' + A + ' varchar(10)' from test1
set @s = @s + ')'
exec(@s)
print @s
--借助中間表實(shí)現(xiàn)行列轉(zhuǎn)換
declare @name varchar(20)

declare t_cursor cursor for
select name from syscolumns
where id=object_id('test1') and colid > 1 order by colid

open t_cursor

fetch next from t_cursor into @name

while @@fetch_status = 0
begin
??? exec('select ' + @name + ' as t into test3 from test1')
??? set @s='insert into test2 select ''' + @name + ''''
??? select @s = @s + ',''' + rtrim(t) + '''' from test3
??? exec(@s)
??? exec('drop table test3')
??? fetch next from t_cursor into @name
end
close t_cursor
deallocate t_cursor

--查看行列互換處理結(jié)果
select * from test1
select * from test2

--刪除表
drop table test1
drop table test2
----------------------------------------------------------------------------
/*固定的寫(xiě)法:*/
select t1.* , t2.y , t3.z from
(select a = 'b' , x = b from test1 where a = 'x') t1,
(select a = 'b' , y = b from test1 where a = 'y') t2,
(select a = 'b' , z = b from test1 where a = 'z') t3
where t1.a = t2.a and t1.a = t2.a
union all
select t1.* , t2.y , t3.z from
(select a = 'c' , x = c from test1 where a = 'x') t1,
(select a = 'c' , y = c from test1 where a = 'y') t2,
(select a = 'c' , z = c from test1 where a = 'z') t3
where t1.a = t2.a and t1.a = t2.a
union all
select t1.* , t2.y , t3.z from
(select a = 'd' , x = d from test1 where a = 'x') t1,
(select a = 'd' , y = d from test1 where a = 'y') t2,
(select a = 'd' , z = d from test1 where a = 'z') t3
where t1.a = t2.a and t1.a = t2.a
union all
select t1.* , t2.y , t3.z from
(select a = 'e' , x = e from test1 where a = 'x') t1,
(select a = 'e' , y = e from test1 where a = 'y') t2,
(select a = 'e' , z = e from test1 where a = 'z') t3
where t1.a = t2.a and t1.a = t2.a

----------------------------------------------------------------------------
/*
表tb,數(shù)據(jù)如下:
項(xiàng)目種類? 業(yè)績(jī)? 提成
洗吹類　　200?? 10
外賣????? 100?? 5
合計(jì)????? 300?? 15
轉(zhuǎn)換成:
項(xiàng)目種類? 洗吹類? 外賣? 合計(jì)
業(yè)績(jī)????? 200???? 100?? 300
提成????? 10????? 5???? 15
*/

create table tb
(
? 項(xiàng)目種類 varchar(10),
? 業(yè)績(jī)???? int,
? 提成???? int
)

insert into tb(項(xiàng)目種類,業(yè)績(jī),提成) values('洗吹類',200,10)
insert into tb(項(xiàng)目種類,業(yè)績(jī),提成) values('外賣'? ,100,5)
insert into tb(項(xiàng)目種類,業(yè)績(jī),提成) values('合計(jì)'? ,300,15)
go

select 項(xiàng)目種類,sum(洗吹類) as 洗吹類 , sum(外賣) as 外賣 , sum(合計(jì)) as 合計(jì) from
(
? select 項(xiàng)目種類 = '業(yè)績(jī)',
???????? 洗吹類?? = case when 項(xiàng)目種類 = '洗吹類' then 業(yè)績(jī) else 0 end,
???????? 外賣???? = case when 項(xiàng)目種類 = '外賣'?? then 業(yè)績(jī) else 0 end,
???????? 合計(jì)???? = case when 項(xiàng)目種類 = '合計(jì)'?? then 業(yè)績(jī) else 0 end
? from tb
union all
? select 項(xiàng)目種類 = '提成' ,
???????? 洗吹類?? = case when 項(xiàng)目種類 = '洗吹類' then 提成 else 0 end,
???????? 外賣???? = case when 項(xiàng)目種類 = '外賣'?? then 提成 else 0 end,
???????? 合計(jì)???? = case when 項(xiàng)目種類 = '合計(jì)'?? then 提成 else 0 end
? from tb
) m
group by 項(xiàng)目種類
order by 項(xiàng)目種類 desc

drop table tb

/*
項(xiàng)目種類洗吹類????? 外賣??????? 合計(jì)?????????
-------- ----------- ----------- -----------
業(yè)績(jī)???? 200???????? 100???????? 300
提成???? 10????????? 5?????????? 15

（所影響的行數(shù)為 2 行）
*/

--------------------------------------------------------------------------
/*
數(shù)據(jù)庫(kù)中tb表格如下

月份??? 工資?? 福利? 獎(jiǎng)金
1月???? 100??? 200?? 300
2月???? 110??? 210?? 310
3月???? 120??? 220?? 320
4月???? 130??? 230?? 330

我想得到的結(jié)果是

項(xiàng)目?? 1月??? 2月? 3月? 4月
工資?? 100??? 110? 120? 130
福利?? 200??? 210? 220? 230
獎(jiǎng)金?? 300??? 310? 320? 330

就是說(shuō)完全把表格的行列顛倒，有點(diǎn)像那種旋轉(zhuǎn)矩陣，請(qǐng)問(wèn)如何用sql 語(yǔ)句實(shí)現(xiàn)？
*/

if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[p_zj]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_zj]
GO
/*--行列互換的通用存儲(chǔ)過(guò)程(原著:鄒建):將指定的表,按指定的字段進(jìn)行行列互換*/

create proc p_zj
?????? @tbname sysname, --要處理的表名
?????? @fdname sysname, --做為轉(zhuǎn)換的列名
?????? @new_fdname sysname='' --為轉(zhuǎn)換后的列指定列名
as
declare @s1 varchar(8000) , @s2 varchar(8000),
??????? @s3 varchar(8000) , @s4 varchar(8000),
??????? @s5 varchar(8000) , @i varchar(10)
select @s1 = '' , @s2 = '' , @s3 = '' , @s4 = '' , @s5 = '' , @i = '0'
select @s1 = @s1 + ',@' + @i + ' varchar(8000)',
?????? @s2 = @s2 + ',@' + @i + '=''' + case isnull(@new_fdname , '') when '' then ''
?????? else @new_fdname + '=' end + '''''' + name + '''''''',
?????? @s3 = @s3 + 'select @' + @i + '=@' + @i + '+'',['' + [' + @fdname +
?????? ']+'']=''+cast([' + name + '] as varchar) from [' + @tbname + ']',
?????? @s4 = @s4 + ',@' + @i + '=''select ''+@' + @i,
?????? @s5 = @s5 + '+'' union all ''+@' + @i,
?????? @i=cast(@i as int)+1
from syscolumns
where object_id(@tbname)=id and name<>@fdname

select @s1=substring(@s1,2,8000),
?????? @s2=substring(@s2,2,8000),
?????? @s4=substring(@s4,2,8000),
?????? @s5=substring(@s5,16,8000)
exec('declare ' + @s1 + 'select ' + @s2 + @s3 + 'select ' + @s4 + '
exec(' + @s5 + ')')
go

--用上面的存儲(chǔ)過(guò)程測(cè)試：

create table Test(月份 varchar(4), 工資 int, 福利 int, 獎(jiǎng)金 int)
insert Test
select '1月',100,200,300 union all
select '2月',110,210,310 union all
select '3月',120,220,320 union all
select '4月',130,230,330
go

exec p_zj 'Test', '月份' , '項(xiàng)目'

drop table Test
drop proc p_zj

/*
項(xiàng)目?? 1月???????? 2月???????? 3月???????? 4月?????????
---- ----------- ----------- ----------- -----------
福利?? 200???????? 210???????? 220???????? 230
工資?? 100???????? 110???????? 120???????? 130
獎(jiǎng)金?? 300???????? 310???????? 320???????? 330

（所影響的行數(shù)為 3 行）
*/

/*
靜態(tài)寫(xiě)法(SQL2005)
*/
--測(cè)試環(huán)境
create table Test(月份 varchar(4), 工資 int, 福利 int, 獎(jiǎng)金 int)
insert Test
select '1月',100,200,300 union all
select '2月',110,210,310 union all
select '3月',120,220,320 union all
select '4月',130,230,330
go
--測(cè)試語(yǔ)句
SELECT * FROM
(
? SELECT 考核月份,月份,金額 FROM
???? (SELECT 月份, 工資, 福利, 獎(jiǎng)金 FROM Test) p
? UNPIVOT
???? (金額 FOR 考核月份 IN (工資, 福利, 獎(jiǎng)金))AS unpvt
) T
PIVOT
(MAX(金額)? FOR 月份 in ([1月],[2月],[3月],[4月]))AS pt

--測(cè)試結(jié)果

/*
考核月份? 1月???? 2月????? 3月???? 4月
-------? -----? -----?? ------? -------
福利200210220230
工資100110120130
獎(jiǎng)金300310320330
*/

--刪除環(huán)境
Drop table Test