【SQL】Oralce中使用wmsys.wm_concat函数解决行列转换问题

刚刚在论坛上看到一位朋友的求助帖的问题，在两个表记录合并的同时，需要用到数据的行列转换。

其实，从oracle 10g开始oracle本身已经提供了一个叫wmsys.wm_concat 的函数，可以轻松是实现数据的行列转换。

因这个函数不是很常用，这里简单记录一下。

论坛帖子地址： http://www.itpub.net/forum.php?mod=viewthread&tid=1505787&page=1&extra=#pid18471025

我的问题解答帖

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

楼主如果使用的是oracle 10g以上版本的话，可以使用wmsys.wm_concat函数提供的行列转换功能来实现。
楼主的需求描述的不是很清楚，可以看一下，下面这种是否是你想要的情况。
----------------
SQL> create table a (
  2      tbaId varchar(10),
  3      code  varchar(10),
  4      name  varchar(20)
  5  );

表已创建。

SQL> create table b (
  2      tbbId     varchar(10),
  3      typeId    varchar(10),
  4      comments  varchar(20)
  5  );

表已创建。

SQL> insert into a values('1','1','计算机');

已创建 1 行。

SQL> insert into a values('2','3','语文');

已创建 1 行。

SQL> insert into a values('3','4','数学');

已创建 1 行。

SQL> insert into b values('1','1','易通卡');

已创建 1 行。

SQL> insert into b values('2','1','外劳卡');

已创建 1 行。

SQL> insert into b values('3','2','苹果');

已创建 1 行。

SQL> insert into b values('4','3','钢笔');

已创建 1 行。

SQL> insert into b values('5','3','毛笔');

已创建 1 行。

SQL> insert into b values('6','4','衣服');

已创建 1 行。

SQL> commit;

提交完成。

SQL> select * from a order by tbaId;

TBAID      CODE       NAME
---------- ---------- --------------------
1          1          计算机
2          3          语文
3          4          数学

已选择3行。

SQL> select * from b order by tbbId;

TBBID      TYPEID     COMMENTS
---------- ---------- --------------------
1          1          易通卡
2          1          外劳卡
3          2          苹果
4          3          钢笔
5          3          毛笔
6          4          衣服

已选择6行。

SQL> select a.code,a.name,wm_concat(b.comments)
  2  from a,b
  3  where a.code = b.typeId
  4  group by a.code,a.name
  5  order by a.code;

CODE       NAME                 WM_CONCAT(B.COMMENTS)
---------- -------------------- ----------------------------------------------------
1          计算机               易通卡,外劳卡
3          语文                 钢笔,毛笔
4          数学                 衣服

已选择3行。

SQL>
--------------------------------------------------------------------

---------


以下转载一篇关于wm_concat函数用法的博文：

原文地址： http://www.cnblogs.com/mikemao/archive/2009/06/11/1501116.html

使用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

以上。

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

转载于:http://blog.itpub.net/20335819/viewspace-709883/