為了增強應用的可擴充套件性，有時會把某一列的資料，單獨儲存到一個子表中，比如使用者表（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>

由此可見，針對以上分組需求，兩種方式都可以方便實現，不再需要寫單獨的程式程式碼來解決這種顯示問題。

SQL 中使用WMSYS.WM_CONCAT把列轉為行來顯示

相關文章