oracle行列轉換-多行轉換成字串

pwz1688發表於2014-01-03
Oracle字串
建表語句及初始化資料指令碼如下：

點選(此處)摺疊或開啟

  1. SQL> create table t_row_str(
  2.   2 id int,
  3.   3 col varchar2(10));

  5. 表已建立。

  7. SQL> insert into t_row_str values(1,'a');

  9. 已建立 1 行。

  11. SQL> insert into t_row_str values(1,'b');

  13. 已建立 1 行。

  15. SQL> insert into t_row_str values(1,'c');

  17. 已建立 1 行。

  19. SQL> insert into t_row_str values(2,'a');

  21. 已建立 1 行。

  23. SQL> insert into t_row_str values(2,'d');

  25. 已建立 1 行。

  27. SQL> insert into t_row_str values(2,'e');

  29. 已建立 1 行。

  31. SQL> insert into t_row_str values(3,'c');

  33. 已建立 1 行。

  35. SQL> commit;

  37. 提交完成。

  39. SQL> select * from t_row_str;

  41.         ID COL
  42. ---------- ----------

  44.          1 a
  45.          1 b
  46.          1 c
  47.          2 a
  48.          2 d
  49.          2 e
  50.          3 c

  52. 已選擇7行。
多行轉換成字串有以下多種方式
1)MAX + decode
適用範圍：8i,9i,10g及以後版本

點選(此處)摺疊或開啟

  1. SQL> select id,max(decode(rn,1,col,null))||
  2.   2 max(decode(rn,2,','||col,null))||
  3.   3 max(decode(rn,3,','||col,null)) str
  4.   4 from(select id,col,row_number() over(partition by id order by col) as rn
  5.   5 from t_row_str) t
  6.   6 group by id
  7.   7 order by 1;

  9.         ID STR
  10. ---------- --------------------------------

  12.          1 a,b,c
  13.          2 a,d,e
  14.          3 c
2)row_number + lead(適用範圍：8i,9i,10g及以後版本)

點選(此處)摺疊或開啟

  1. SQL> select id,str from (select id,row_number() over(partition by id order by col) as
  2.   2 rn,
  3.   3 col||lead(','||col,1) over(partition by id order by col)||lead(','||col,2) over(partition by id
  4.  order by col) as str
  5.   4 from t_row_str)
  6.   5 where rn=1
  7.   6 order by 1
  8.   7 /

  10.         ID STR
  11. ---------- --------------------------------

  13.          1 a,b,c
  14.          2 a,d,e
  15.          3 c
注：oracle的lead函式是偏移量函式，其用途是訪問某一欄位的下一個值（lag函式用法一樣，只是訪問上一個值）。如下程式碼所示：

點選(此處)摺疊或開啟

  1. SQL> select id,col,lead(col,1) over(partition by id order by col) lead_1,
  2.   2 lead(col,2) over(partition by id order by col) lead_2,
  3.   3 lead(col,3) over(partition by id order by col) lead_3
  4.   4 from t_row_str;

  6.         ID COL LEAD_1 LEAD_2 LEAD_3
  7. ---------- ---------- ---------- ---------- ----------

  9.          1 a b c
  10.          1 b c
  11.          1 c
  12.          2 a d e
  13.          2 d e
  14.          2 e
  15.          3 c

  17. 已選擇7行。

3)MODEL
適用範圍：10g及以後版本

點選(此處)摺疊或開啟

  1. SQL> SELECT id, substr(str, 2) str FROM t_row_str
  2.   2 MODEL
  3.   3 RETURN UPDATED ROWS
  4.   4 PARTITION BY(ID)
  5.   5 DIMENSION BY(row_number() over(PARTITION BY ID ORDER BY col) AS rn)
  6.   6 MEASURES (CAST(col AS VARCHAR2(20)) AS str)
  7.   7 RULES UPSERT
  8.   8 ITERATE(3) UNTIL( presentv(str[iteration_number+2],1,0)=0)
  9.   9 (str[0] = str[0] || ',' || str[iteration_number+1])
  10.  10 ORDER BY 1;

  12.         ID STR
  13. ---------- --------------------------------------

  15.          1 a,b,c
  16.          2 a,d,e
  17.          3 c

4)sys_connect_by_path
適用範圍：8i,9i,10g及以後版本

點選(此處)摺疊或開啟

  1. SQL> edit;
  2. 已寫入 file afiedt.buf

  4.   1 SELECT t.id id, MAX(substr(sys_connect_by_path(t.col, ','), 2)) str
  5.   2 FROM (SELECT id, col, row_number() over(PARTITION BY id ORDER BY col) rn
  6.   3 FROM t_row_str) t
  7.   4 START WITH rn = 1
  8.   5 CONNECT BY rn = PRIOR rn + 1
  9.   6 AND id = PRIOR id
  10.   7* GROUP BY t.id
  11. SQL> /

  13.        ID STR
  14. ---------- --------------------------------------

  16.          1 a,b,c
  17.          2 a,d,e
  18.          3 c
  20. 以下程式碼適用於10G及之後的版本
  21. SQL> edit
  22. 已寫入 file afiedt.buf

  24.   1 SELECT t.id id, substr(sys_connect_by_path(t.col, ','), 2) str
  25.   2 FROM (SELECT id, col, row_number() over(PARTITION BY id ORDER BY col) rn
  26.   3 FROM t_row_str) t
  27.   4 where connect_by_isleaf=1
  28.   5 START WITH rn = 1
  29.   6 CONNECT BY rn = PRIOR rn + 1
  30.   7* AND id = PRIOR id
  31. SQL> /

  33.        ID STR
  34. ---------- --------------------------------------

  36.          1 a,b,c
  37.          2 a,d,e
  38.          3 c
注：sys_connect_by_path函式使用之前，必須先構造出一個樹，否則無法使用，上例用row_number函式構造樹型結構。
5)wmsys.wm_concat
適用範圍：10g及以後版本
這個函式預定義按','分隔字串，若要用其他符號分隔可以用，replace將','替換。

點選(此處)摺疊或開啟

  1. SQL> select id,wmsys.wm_concat(col) from t_row_str
  2.   2 group by id;

  4.         ID
  5. ----------

  7. WMSYS.WM_CONCAT(COL)
  8. -------------------------------------------------------

  10.          1
  11. a,b,c

  13.          2
  14. a,d,e

  16.          3
  17. c




來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/21251711/viewspace-1066878/，如需轉載，請註明出處，否則將追究法律責任。

相關文章