使用listagg函式完成行列轉換一例

在程式開發中，經常遇到需把多行的資料在一行中顯示出來，也就是常見的行列轉換，例如：源資料為：

BSC_ID	ATTR_NAME	VALUE
8f33773a-d93b-4433-891b-672dd94441b4	regular	1.5,1.6
	type	無
	weight	25
	time	2015-04-30
	madeof	中國
	length	2.5

計劃按如下格式顯示出來：

BSC_ID	regular	type	weight	time	madeof	length
8f33773a-d93b-4433-891b-672dd94441b4	1.5,1.6	無	25	2015-04-30	中國	2.5

在PB 開發中，可以使用CrossTab 型別的資料視窗來實現。本文將介紹如何在單條SQL實現。

整個方法的思路還是透過Group By 和Decode 語句來實現，例如為提取regular 的值，可以對每列decode(attr_name, 'regular', value, '')按BSC_ID分組求聚合，也就是字串相加。 對於數字求和，可以使用Sum函式，對於字串求和，9i 開始可以使用 自定義聚合函式（）來實現。10G 中在新版電子病歷系統中，使用的是一個未公開的函式wmsys.wm_concat,這種方法在日常資料維護時可以使用，但不適合在程式中使用。 到了11G, 終於提供了listagg函式完成字串的相加功能。 具體可參考如下實現：

 

SQL>  select bsc_id , attr_name , value from KVPAIR_DATA;

BSC_ID                                   ATTR_NAME  VALUE

---------------------------------------- ---------- ----------

8f33773a-d93b-4433-891b-672dd94441b4     regular    1.5

8f33773a-d93b-4433-891b-672dd94441b4     type       無

8f33773a-d93b-4433-891b-672dd94441b4     weight     25

8f33773a-d93b-4433-891b-672dd94441b4     time       2015-04-30

8f33773a-d93b-4433-891b-672dd94441b4     madeof     中國

8f33773a-d93b-4433-891b-672dd94441b4     length     2.5

8f33773a-d93b-4433-891b-672dd94441b4     regular    1.6

7 rows selected.

SQL> select bsc_id,

  2         listagg(decode(attr_name, 'regular', value, ''), ',')

  3                  within group(order by bsc_id) "regular",

  4         listagg(decode(attr_name, 'type', value, ''), '')

  5                  within group(order by bsc_id) "type",

  6         listagg(decode(attr_name, 'weight', value, ''), '')

  7                 within group(order by bsc_id) "weight",

  8         listagg(decode(attr_name, 'time', value, ''), ',')

  9                within group(order by bsc_id) "time",

 10         listagg(decode(attr_name, 'madeof', value, ''), ',')

 11                   within group(order by bsc_id) "madeof",

 12         listagg(decode(attr_name, 'length', value, ''), ',')

 13                  within group(order by bsc_id) "length"

 14    from KVPAIR_DATA

 15   group by bsc_id;

BSC_ID                                   regular    type       weight

---------------------------------------- ---------- ---------- ----------

time       madeof     length

---------- ---------- ----------

8f33773a-d93b-4433-891b-672dd94441b4     1.5,1.6    無         25

2015-04-30 中國       2.5

1 row selected.