sql server和oracle行轉列的一種典型方法

前言：網上有不少文章是講行轉列的，但是大部分都是直接貼代碼，忽視了中間過程，本人自己思考了下為什麼要這樣實現，並且做了如下的筆記，對有些懂的人來說可能沒有價值，希望對還不懂的人有一點借鑒意義。

對於有些業務來說，資料在表中的儲存和其最終的Grid表現恰好相當於把源表倒轉，那麼這個時候我們就碰到了如何把行轉化為列的問題，為了簡化問題，我們且看如下查詢出來的資料，您不必關心表的設計以及sql語句：

假設用到的sql語句為：

SELECT [姓名],[時代],[金錢]
  FROM [test].[dbo].[people]  

 

這個表格儲存體了兩個人在不同時代（時代是固定的三個：年輕、中年和老年）擁有的金幣，其中：

張三在年輕、中年和老年時期分別擁有1000、5000、800個金幣；

李四在年輕、中年和老年時期分別擁有1200、6000、500個金幣。

現在我們想把兩人在不同階段擁有的金幣用類似如下的表格來展現：

姓名	年輕	中年	老年
張三	1000	5000	800
李四	1200	6000	500

 

我們現在考慮用最簡單和直接的辦法來實現，其實關鍵是如何建立那些需要增加的列，且如何設定其值，現在我們來建立“年輕”列，關鍵的問題是，這一列的值如何設定？合法的邏輯應該是這樣：如果該行不是“年輕”時代，那麼其“金錢”我們認為是0，那麼sql語句如何寫呢？

如果是用的sql server，那麼肯定要用到case了：

 

case  [時代] when '年輕' then [金錢] else 0 end as 年輕

或

case when  [時代]= '年輕' then [金錢] else 0 end as 年輕

 

如果用的是oracle，那麼要用到decode函數，decode(1+1,3,'錯',2,'是',5,'錯','都不滿足下返回的值')，這個函數將返回“是”，具體用法限於篇幅這裡不再介紹，相信大家從這個式子可以大概瞭解到其意思，用decode建立“年輕”列的句子是：完整的sql語句如下所示：

decode(時代,'年輕',金錢,0)) 年輕

 

SELECT [姓名],[時代],[金錢], 

case  [時代] when '年輕' then [金錢] else 0 end as 年輕,
case  [時代] when '中年' then [金錢] else 0 end as 中年,
case  [時代] when '老年' then [金錢] else 0 end as 老年 

  FROM [test].[dbo].[people] 

 

現在我們來看看其執行結果：

相信看到這個結果，大家都知道下一步該做什麼，那就是分組：按姓名分組，並且對三個時代的金錢進行求和：

select [姓名],sum([年輕]) as 年輕,sum([中年]) as 中年,sum([老年]) as 老年 from
(SELECT [姓名],[時代],[金錢], 

case  [時代] when '年輕' then [金錢] else 0 end as 年輕,
case  [時代] when '中年' then [金錢] else 0 end as 中年,
case  [時代] when '老年' then [金錢] else 0 end as 老年 

  FROM [test].[dbo].[people]) t
  group by [姓名]

這裡用到了子查詢，是為了邏輯更清晰一點，其實可以不用子查詢；至於oracle下的sql語句，除了要使用decode之外，其餘幾乎一致，本人正是在oracle中實現之後才研究了下sql server下的實現方式。

最後看看結果：

事實上，當列不固定的時候，比如除了“年輕”、“中年”、“老年”以外還有其他的未知的時代，實現思路其實基本一致，只是需要動態產生sql而已。