ORACLE分析函数--使用listagg实现行列转换           listagg函数是在oracle11g中新引入的聚合函数，当然也可以作为分析函数来使用。该函数可以基于分组内特定列的排序，将测量列进行拼接合并。         示例：         在10g中，该函数不被支持

[sql] SQL> select prod_id, LISTAGG(cust_first_name||'
'
||cust_last_name,'
;
'
) WITHIN GROUP (order by amount_sold) cust_name from sales, customers 2 where sales.cust_id = customers.cust_id and cust_gender = '
M'
and cust_credit_limit = 15000 and prod_id between 15 and 18 and channel_id =2 and time_id > to_date('
01-06-01'
,'
YY-MM-DD'
) group by prod_id;
3 4 5 select prod_id, LISTAGG(cust_first_name||'
'
||cust_last_name,'
;
'
) WITHIN GROUP (order by amount_sold) cust_name from sales, customers *

 

第 1 行出现错误:   ORA-00923: 未找到要求的 FROM 关键字           在11g中，运行结果如下：

[sql] SQL> l 1 select prod_id, LISTAGG(cust_first_name||'
'
||cust_last_name,'
;
'
) WITHIN GROUP (order by amount_sold) cust_name from sales, customers 2 where sales.cust_id = customers.cust_id and cust_gender = '
M'
3 and cust_credit_limit = 15000 and prod_id between 15 and 18 4 and channel_id =2 and time_id > to_date('
01-06-01'
,'
YY-MM-DD'
) 5* group by prod_id SQL> / PROD_ID CUST_NAME ---------- ---------------------------------------------------------------------------------------------------- 15 Mason Murray;
Helga Nickols;
Roxanne Crocker;
Glenn Wong;
Roxanne Crocker;
Franklin Hagan;
Franklin Hagan;
Ransom Wiser;
Reyburn Markman 16 Forrest Lindsey;
Helga Nickols;
Helga Nickols;
Roxanne Crocker;
Garrett Manson;
Roxanne Crocker;
Franklin Hagan;
Prane Oppy;
Franklin Hagan;
Bud Smyth;
Reyburn Markman 17 Garrett Manson;
Roxanne Crocker;
Helga Nickols;
Roxanne Crocker;
Helga Nickols;
Mason Murray;
Ethan Jeffre ys;
Franklin Hagan;
Prane Oppy;
Royd Ricketts 18 Erik Ready;
Garrett Manson;
Forrest Lindsey;
Franklin Hagan;
Franklin Hagan;
Reyburn Markman

 

          当然，listagg也可以基于over()的分区。         示例：         

[sql] SQL> l 1 select time_id,prod_id,min(amount_sold),listagg(min(amount_sold),'
;
'
) within group (order by prod_id) over (partition by time_id) cust_list 2 from sales where time_id > to_date('
20-DEC-01'
,'
DD-MON-YY'
,'
NLS_DATE_LANGUAGE = American'
) and prod_id between 120 and 125 3* group by prod_id,time_id SQL> / TIME_ID PROD_ID MIN(AMOUNT_SOLD) CUST_LIST ------------------- ---------- ---------------- ---------------------------------------------------------------------- 2001-12-21 00:00:00 120 51.36 51.36;
10.81 2001-12-21 00:00:00 121 10.81 51.36;
10.81 2001-12-22 00:00:00 120 51.36 51.36;
10.81;
20.23;
56.12;
17.79;
15.67 2001-12-22 00:00:00 121 10.81 51.36;
10.81;
20.23;
56.12;
17.79;
15.67 2001-12-22 00:00:00 122 20.23 51.36;
10.81;
20.23;
56.12;
17.79;
15.67 2001-12-22 00:00:00 123 56.12 51.36;
10.81;
20.23;
56.12;
17.79;
15.67 2001-12-22 00:00:00 124 17.79 51.36;
10.81;
20.23;
56.12;
17.79;
15.67 2001-12-22 00:00:00 125 15.67 51.36;
10.81;
20.23;
56.12;
17.79;
15.67 2001-12-23 00:00:00 120 51.36 51.36;
10.49;
20.23;
57.86;
17.79;
15.67 2001-12-23 00:00:00 121 10.49 51.36;
10.49;
20.23;
57.86;
17.79;
15.67 2001-12-23 00:00:00 122 20.23 51.36;
10.49;
20.23;
57.86;
17.79;
15.67 TIME_ID PROD_ID MIN(AMOUNT_SOLD) CUST_LIST ------------------- ---------- ---------------- ---------------------------------------------------------------------- 2001-12-23 00:00:00 123 57.86 51.36;
10.49;
20.23;
57.86;
17.79;
15.67 2001-12-23 00:00:00 124 17.79 51.36;
10.49;
20.23;
57.86;
17.79;
15.67 2001-12-23 00:00:00 125 15.67 51.36;
10.49;
20.23;
57.86;
17.79;
15.67 2001-12-24 00:00:00 120 51.36 51.36;
10.49;
20.23;
56.12;
17.79;
15.67 2001-12-24 00:00:00 121 10.49 51.36;
10.49;
20.23;
56.12;
17.79;
15.67 2001-12-24 00:00:00 122 20.23 51.36;
10.49;
20.23;
56.12;
17.79;
15.67 2001-12-24 00:00:00 123 56.12 51.36;
10.49;
20.23;
56.12;
17.79;
15.67 2001-12-24 00:00:00 124 17.79 51.36;
10.49;
20.23;
56.12;
17.79;
15.67 2001-12-24 00:00:00 125 15.67 51.36;
10.49;
20.23;
56.12;
17.79;
15.67 2001-12-25 00:00:00 120 51.36 51.36;
10.81 2001-12-25 00:00:00 121 10.81 51.36;
10.81 TIME_ID PROD_ID MIN(AMOUNT_SOLD) CUST_LIST ------------------- ---------- ---------------- ---------------------------------------------------------------------- 2001-12-26 00:00:00 123 57.86 57.86 2001-12-27 00:00:00 120 51.36 51.36;
10.49;
20.23;
56.12;
17.79;
15.67 2001-12-27 00:00:00 121 10.49 51.36;
10.49;
20.23;
56.12;
17.79;
15.67 2001-12-27 00:00:00 122 20.23 51.36;
10.49;
20.23;
56.12;
17.79;
15.67 2001-12-27 00:00:00 123 56.12 51.36;
10.49;
20.23;
56.12;
17.79;
15.67 2001-12-27 00:00:00 124 17.79 51.36;
10.49;
20.23;
56.12;
17.79;
15.67 2001-12-27 00:00:00 125 15.67 51.36;
10.49;
20.23;
56.12;
17.79;
15.67 2001-12-28 00:00:00 120 51.36 51.36;
10.49;
56.12;
17.79;
15.67 2001-12-28 00:00:00 121 10.49 51.36;
10.49;
56.12;
17.79;
15.67 2001-12-28 00:00:00 123 56.12 51.36;
10.49;
56.12;
17.79;
15.67 2001-12-28 00:00:00 124 17.79 51.36;
10.49;
56.12;
17.79;
15.67 TIME_ID PROD_ID MIN(AMOUNT_SOLD) CUST_LIST ------------------- ---------- ---------------- ---------------------------------------------------------------------- 2001-12-28 00:00:00 125 15.67 51.36;
10.49;
56.12;
17.79;
15.67 2001-12-29 00:00:00 122 20.23 20.23;
56.12;
17.79;
15.67 2001-12-29 00:00:00 123 56.12 20.23;
56.12;
17.79;
15.67 2001-12-29 00:00:00 124 17.79 20.23;
56.12;
17.79;
15.67 2001-12-29 00:00:00 125 15.67 20.23;
56.12;
17.79;
15.67 2001-12-30 00:00:00 120 51.36 51.36;
10.49 2001-12-30 00:00:00 121 10.49 51.36;
10.49 2001-12-31 00:00:00 121 10.81 10.81

 

   已选择41行。