因这个函数不是很常用,这里简单记录一下。
楼主的需求描述的不是很清楚,可以看一下,下面这种是否是你想要的情况。
----------------
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函数用法的博文:
使用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/