行列轉換,通常有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 <> 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 ' )
?
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
