# 行列转换写法小结

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

(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) 动态

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) 动态

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

(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) 动态

declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select ID , [Code] = ' + quotename(Name , '''') + ' , [Value] = ' + quotename(Name) + ' from ColToRow'
from syscolumns
where name <> N'ID' 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 <> N'ID' and ID = object_id('ColToRow')
set @sql = '[' + @sql + ']'
--print @sql
exec('select ID , Code , Value from ColToRow unpivot (Value for Code in(' + @sql + ')) t')