使用WMSYS.WM_CONCAT函数实现行列转换

kingsleylong

转自： http://blog.csdn.net/zhpsam109/article/details/1917011
 
SQL> select version from v$instance;

 

VERSION

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

10.2.0.1.0

 

SQL> 

SQL> create table IDTABLE

  2  (

  3    id  number,

  4    val varchar2(20)

  5  )

  6  ;

 

Table created

 

SQL> 

SQL> insert into IDTABLE (ID, VAL)

  2  values (10, 'abc');

 

1 row inserted

SQL> insert into IDTABLE (ID, VAL)

  2  values (10, 'abc');

 

1 row inserted

SQL> insert into IDTABLE (ID, VAL)

  2  values (10, 'def');

 

1 row inserted

SQL> insert into IDTABLE (ID, VAL)

  2  values (10, 'def');

 

1 row inserted

SQL> insert into IDTABLE (ID, VAL)

  2  values (20, 'ghi');

 

1 row inserted

SQL> insert into IDTABLE (ID, VAL)

  2  values (20, 'jkl');

 

1 row inserted

SQL> insert into IDTABLE (ID, VAL)

  2  values (20, 'mno');

 

1 row inserted

SQL> insert into IDTABLE (ID, VAL)

  2  values (20, 'mno');

 

1 row inserted

 

SQL> select id,val from idtable;

 

        ID VAL

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

        10 abc

        10 abc

        10 def

        10 def

        20 ghi

        20 jkl

        20 mno

        20 mno

 

8 rows selected

 

SQL> commit;

 

Commit complete

 

SQL> 

SQL> SELECT ID, 

WMSYS.WM_CONCAT(VAL) 

AS ENAMES

  2    FROM IDTABLE

  3   GROUP BY ID;

 

        ID ENAMES

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

        10 abc,abc,def,def

        20 ghi,jkl,mno,mno

 

SQL> 

SQL> SELECT ID, WMSYS.WM_CONCAT(DISTINCT VAL) AS ENAMES

  2    FROM IDTABLE

  3   GROUP BY ID

  4   ORDER BY ID;

 

        ID ENAMES

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

        10 abc,def

        20 ghi,jkl,mno

 

SQL> 

SQL> SELECT ID, VAL, WMSYS.WM_CONCAT(VAL) OVER(PARTITION BY ID) AS ENAMES

  2    FROM IDTABLE

  3   ORDER BY ID;

 

        ID VAL               ENAMES

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

        10 abc                abc,abc,def,def

        10 abc                abc,abc,def,def

        10 def                abc,abc,def,def

        10 def                abc,abc,def,def

        20 ghi                ghi,jkl,mno,mno

        20 jkl                 ghi,jkl,mno,mno

        20 mno               ghi,jkl,mno,mno

        20 mno               ghi,jkl,mno,mno

 

8 rows selected

 

SQL> 

SQL> SELECT ID, VAL, WMSYS.WM_CONCAT(VAL) OVER(ORDER BY ID, VAL) AS ENAMES

  2    FROM IDTABLE

  3   ORDER BY ID;

 

        ID VAL               ENAMES

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

        10 abc                abc,abc

        10 abc                abc,abc

        10 def                abc,abc,def,def

        10 def                abc,abc,def,def

        20 ghi                abc,abc,def,def,ghi

        20 jkl                 abc,abc,def,def,ghi,jkl

        20 mno               abc,abc,def,def,ghi,jkl,mno,mno

        20 mno               abc,abc,def,def,ghi,jkl,mno,mno

 

8 rows selected

 

SQL>