SQL 中使用WMSYS.WM_CONCAT把列转为行来显示

为了增强应用的可扩展性，有时会把某一列的数据，单独存储到一个子表中，比如用户表（t_user）和角色表（t_role），为了保存他们之间的对应关系，需要设计一个中间表（t_userrole）用来保存用户与角色的对应关系。假如需要查看每个用户对应的角色，这时就需要把每个用户对应的一个或多个角色拼到一起，作为一个字段显示出来，下面举例说明两种实现方式。

 

create table test(id,name) as
select 10, 'david' from dual
union all
select 10, 'john' from dual
union all
select 10, 'jerry' from dual
union all
select 20, 'smith' from dual
union all
select 20, 'scott' from dual
union all
select 30, 'tony' from dual
union all
select 30, 'amanda' from dual;

创建测试表，包含2个字段。

 

第一种方式，使用 WMSYS.WM_CONCAT() 函数。

SQL> select id, WMSYS.WM_CONCAT(name) as allname
  2    from test
  3   group by id
  4   order by id;

        ID ALLNAME
---------- --------------------
        10 david,john,jerry
        20 smith,scott
        30 tony,amanda

 

SQL> select WMSYS.WM_CONCAT(name) as allname from test;

ALLNAME
------------------------------------------------------------
david,john,jerry,smith,scott,tony,amanda

SQL>

可以看出，该函数就是把参数指定列按顺序连接起来。

 

第二种方式，使用分组函数 row_number() over()来实现。

SQL> select id, max(sys_connect_by_path(name, ',')) as allname
  2    from (select a.*, row_number() over(partition by id order by id) rn
  3            from test a)
  4   group by id
  5   start with rn = 1
  6  connect by rn - 1 = prior rn
  7         and id = prior id
  8   order by id;

        ID ALLNAME
---------- --------------------
        10 ,david,john,jerry
        20 ,smith,scott
        30 ,tony,amanda

SQL>

 

由此可见，针对以上分组需求，两种方式都可以方便实现，不再需要写单独的程序代码来解决这种显示问题。

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

转载于:http://blog.itpub.net/9399028/viewspace-712936/