使用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 开始可以使用 自定义聚合函数（Aggregate Functions）来实现。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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9036/viewspace-1625061/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9036/viewspace-1625061/