行列转换

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

一. 行转列

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

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

1 2 3 4 5 6 7 8 9 10 11

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语句，然后运行。

1 2 3 4 5 6 7

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

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

(2) 动态

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

1 2 3 4 5

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

二. 列转行

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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

1 2 3 4 5 6

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语句，然后运行。

1 2 3 4 5 6 7

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

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

(2) 动态

1 2 3 4 5 6

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

本文出自 “SQL Server DBA” 博客，请务必保留此出处http://qianzhang.blog.51cto.com/317608/1202793