偽行列轉換！

不知道該起個怎樣的標題，講課時一位學員提出了這樣一個需求，搜了一下可以透過wmsys.wm_concat來實現，但是客戶使用的是9i，於是在群裡的一個哥們的幫助下，透過樹狀sql結合分析函式實現了客戶的要求,不過客戶反應看不懂，後來我還是用cursor笨辦法來實現一下：

[@more@]

SQL> select * from t;

ID NAME AGE
---------- ---------- ----------
2 b 30
2 a 30
1 a 30
1 b 30

SQL> select id,name_path from (select id,name_path,
2 row_number() over(partition by id order by id,curr_level desc) name_path_rank
3 from (select id,name,rank,level as curr_level,
4 ltrim(sys_connect_by_path(name,','),',') name_path from (
5 select id,name,row_number() over(partition by id order by id,name) rank
6 from t order by id,name) connect by id = prior id and rank-1 = prior rank))
7 where name_path_rank=1;

ID NAME_PATH
---------- ----------
1 a,b
2 a,b

SQL>

--==================================

10g的話oracle提供了函式：wmsys.wm_concat可以很容易的實現：

SQL> select * from t;

ID NAME AGE
---------- ---------- ----------
2 b 30
2 a 30
1 a 30
1 b 30

SQL> select id,wmsys.wm_concat(name) a from t group by id;

ID A
---------- ----------
1 a,b
2 b,a

SQL> select id,wmsys.wm_concat(name) a from (select id,name from t order by name) group by id;

ID A
---------- ----------
1 a,b
2 a,b

SQL>

--=============================

SQL> declare
2 v_string varchar2(20) := null;
3 begin
4 for i in (select distinct id from t) loop
5 v_string := null;
6 for j in (select name from t where id=i.id order by name) loop
7 v_string := v_string||j.name||',';
8 end loop;
9 dbms_output.put_line(substr(i.id||' '||v_string,1,length(i.id||' '||v_string) - 1));
10 end loop;
11 end;
12 /
1 a,b
2 a,b

PL/SQL procedure successfully completed.

SQL>