使用WMSYS.WM_CONCAT函數實現行列轉換

Introduction of WMSYS

WMSSYS is used to store all the metadata information for Oracle Workspace Manager. This user was introduced in Oracle9i and (like most Oracle9i supporting accounts) is locked by default. The user account is locked because we want the password to be public but restrict access to the account to the SYS schema. So, to unlock the account, DBA privileges are required.

This post will show you on how to use the method WMSSYS.WM_CONCAT to convert the row and columns in data table.

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