频道栏目
IT货架 > > 正文
03. 行列转换写法小结
网友分享于:Jan 1, 1970 8:00:00 AM    来源: IT货架   

标签:style   class   blog   code   http   ext   

原文:03. 行列转换写法小结

行列转换,通常有2种方法,一是CASE WHEN/UNION;一是PIVOT/UNPIVOT。对于行值或列数不固定的情况,需要用动态SQL。


一. 行转列

--drop table RowToCol
create table RowToCol
(
ID int,
Code varchar(10),
Value int
)
Go
insert RowToCol 
select 1,Item1,1000 union all
select 1,Item2,1000 union all
select 1,Item3,500 union all
select 2,Item1,2000 union all
select 2,Item2,0 union all
select 3,Item1,1000 union all
select 3,Item3,500 
GO
select * from RowToCol

 要得到这样的结果:

ID Item1 Item2 Item3
1 1000 1000 500
2 2000 0 0
3 1000 0 500


1. CASE WHEN
在SQL Server 2000时,常用的写法,沿用至今。
(1) 静态

select ID,
sum(case Code when Item1 then Value else 0 end) as Item1,
sum(case Code when Item2 then Value else 0 end) as Item2,
sum(case Code when Item3 then Value else 0 end) as Item3
from RowToCol group by ID
--或者用max也行
select ID,
max(case Code when Item1 then Value else 0 end) as Item1,
max(case Code when Item2 then Value else 0 end) as Item2,
max(case Code when Item3 then Value else 0 end) as Item3
from RowToCol group by ID

 (2) 动态

在不确定有多少行需要转为列时,先distinct出待转列的值,再拼出包含若干个CASE的SQL语句,然后运行。

declare @sql varchar(8000)
set @sql = select ID 
select @sql = @sql +  , max(case Code when ‘‘‘ + Code + ‘‘‘ then Value else 0 end) [ + Code + ]
from (select distinct Code from RowToCol) as a
set @sql = @sql +  from RowToCol group by ID
--print @sql
exec(@sql)

 

2. PIVOT
PIVOT是SQL Server 2005的新语法,Oracle在11g中也推出了这个语法。
(1) 静态

select * from (select * from RowToCol) a 
pivot (max(value) for Code in ([Item1],[Item2],[Item3])) b

 (2) 动态

用PIVOT拼写动态SQL时就简单了,只要把后面的列清单整理出来就可以了。

declare @sql varchar(8000)
select @sql = isnull(@sql + ],[ , ‘‘) + Code from RowToCol group by Code
set @sql = [ + @sql + ]
--print @sql
exec (select * from (select * from RowToCol) a pivot (max(value) for Code in ( + @sql + )) b)

 

二. 列转行

--drop table ColToRow
create table ColToRow
(
ID int,
Item1 int,
Item2 int,
Item3 int
)
GO
insert into ColToRow
select 1,1000,1000,500 union all
select 2,2000,0,0 union all
select 3,1000,0,500 
GO
select * from ColToRow

 要得到这样的结果:

ID Code Value
1 Item1 1000
1 Item2 1000
1 Item3 500
2 Item1 2000
2 Item2 0
2 Item3 0
3 Item1 1000
3 Item2 0
3 Item3 500

1. UNION
在SQL Server 2000时,常用的写法,沿用至今。
(1) 静态

select ID,Code=Item1,Value=Item1 from ColToRow
union all
select ID,Code=Item2,Value=Item2 from ColToRow
union all
select ID,Code=Item3,Value=Item3 from ColToRow
order by ID

 SQL Server对于多个UNION的排序,只要在最后加ORDER BY就可以了。

(2) 动态
在不确定有多少列需要转为行时,先借助系统表syscolumns找出待转行的列,再拼出包含若干个UNION语句,然后运行。

declare @sql varchar(8000)
select @sql = isnull(@sql +  union all  , ‘‘ ) +  select ID , [Code] =  + quotename(Name , ‘‘‘‘) +  , [Value] =  + quotename(Name) +  from ColToRow
from syscolumns 
where name <> NID and ID = object_id(ColToRow)
order by colid asc
--print @sql
exec(@sql +  order by ID )

 

2. UNPIVOT
UNPIVOT是SQL Server 2005的新语法,Oracle在11g中也推出了这个语法。
(1) 静态

select ID , Code , Value from ColToRow 
unpivot (Value for Code in([Item1] , [Item2] , [Item3])) t

 (2) 动态

declare @sql varchar(8000)
select @sql = isnull(@sql + ],[ , ‘‘) + name from syscolumns 
where name <> NID and ID = object_id(ColToRow)
set @sql = [ + @sql + ]
--print @sql
exec(select ID , Code , Value from ColToRow unpivot (Value for Code in( + @sql + )) t)

 

 

03. 行列转换写法小结,布布扣,bubuko.com

03. 行列转换写法小结

标签:style   class   blog   code   http   ext   

广告服务联系QQ:1134687142 | 网站地图

版权所有: IT货架- 内容来自互联网,仅供用于技术学习,请遵循相关法律法规. 京ICP备11030978号-1