oracle查詢多行資料合併成一行資料

標籤：

如果是oracle 10g以上 一個wm_concat函數就可以解決。

如果是oracle 9i以下就有點麻煩了。

表結構如下：

NAME                            Null           Type------------------------ ---------   -----N_SEC_CODE                 NOT NULL CHAR(6)C_RESEARCHER_CODE    NOT NULL VARCHAR2(20)

 

此表儲存了“股票”與“研究員”的對應關係資料，一般而言，對於同一隻股票而言，可能有多個研究員

對其進行跟蹤研究。所以目前遇到一個要求，需查詢出每隻股票和對應的研究員（研究員代碼間，使用逗號分隔）。

例如有如下資料：

000297                                   chenpeng           

000297                                   liusu              

合并處理後需顯示為：

000297                                   chenpeng,liusu

網上查了很多方法，但通常而言都是編寫自訂多行文本合并函數，或者對支援的列數具有局限性。

最後在英文google中搜到如下比較巧的方法。不用在資料庫中增加function，純SQL一次性搞定，

而且擴充性很好，沒有列的限制。

SELECT    n_sec_code, TRANSLATE (LTRIM (text, ‘/‘), ‘*/‘, ‘*,‘) researcherList     FROM (SELECT ROW_NUMBER () OVER (PARTITION BY n_sec_code ORDER BY n_sec_code,                   lvl DESC) rn,                  n_sec_code, text             FROM (SELECT      n_sec_code, LEVEL lvl,                              SYS_CONNECT_BY_PATH (c_researcher_code,‘/‘) text                         FROM (SELECT    n_sec_code, c_researcher_code as c_researcher_code,                                        ROW_NUMBER () OVER (PARTITION BY n_sec_code ORDER BY n_sec_code,c_researcher_code) x                                   FROM m_researcher_stock_rel                               ORDER BY n_sec_code, c_researcher_code) a                   CONNECT BY n_sec_code = PRIOR n_sec_code AND x - 1 = PRIOR x))    WHERE rn = 1ORDER BY n_sec_code;

 

預想的結果成功出現，多行資料成功匯總到一行，特此分享與大家。對於你自己的應用中，只需要把SQL中“n_sec_code”

換為你的用來匯總的列，“c_researcher_code”替換為需合并文本的列，“m_researcher_stock_rel”替換為你的表名，就是這麼簡單。

SQL分析：

1、利用 “ROW_NUMBER () OVER (PARTITION BY……” 為按“股票代號”匯總後的資料行添加組內序號

2、“SYS_CONNECT_BY_PATH” 按組內序號相鄰關係，為每一層進行不同行的“研究員代碼”疊加

3、再次利用“股票代號”進行組內分組，但按第二部中的層次排倒序，增加調整後等級

4、取所有調整後等級為1的結果，即為所要求的資料行

方法很巧妙，值得學習。

在ORACLE10中可使用以下方法：

SELECT n_sec_code, wmsys.wm_concat (c_researcher_code) as resultFROM m_researcher_stock_relGROUP BY n_sec_code

 

又一個樣本：

WITH tab AS(select ‘01‘ no,‘ss‘ name, 10 jiner,‘麥子‘ you from dual union allselect ‘02‘ no,‘dd‘ name, 11 jiner,‘蘿蔔‘ you from dual union allselect ‘03‘ no,‘cc‘ name, 9 jiner,‘大豆‘ you from dual union allselect ‘01‘ no,‘ss‘ name, 10 jiner,‘白菜‘ you from dual)SELECT a.no, a.name, a.jiner, ltrim(MAX(sys_connect_by_path(you, ‘,‘)), ‘,‘) youFROM (SELECT row_number() over(PARTITION BY a.no, a.name, a.jiner          ORDER BY a.no, a.name, a.jiner) rn,a.*          FROM tab a) aSTART WITH rn = 1CONNECT BY PRIOR rn = rn - 1 AND           a.no = PRIOR a.no AND           a.name = PRIOR a.name AND           a.jiner = PRIOR a.jinerGROUP BY a.no, a.name, a.jiner

 

oracle wm_concat函數，用於列轉行，逗號分隔

首先讓我們來看看這個神奇的函數wm_concat(列名),該函數可以把列值以","號分隔起來,並顯示成一行,接下來上例子,看看這個神奇的函數如何應用

準備測試資料

SQL> create table test(id number,name varchar2(20));SQL> insert into test values(1,‘a‘);SQL> insert into test values(1,‘b‘);SQL> insert into test values(1,‘c‘);SQL> insert into test values(2,‘d‘);SQL> insert into test values(2,‘e‘);SQL> commit;

 

效果1 : 行轉列

SQL> select wm_concat(name) from test;WM_CONCAT(NAME)-------------------------------------------------------------------------a,b,c,d,e

 

效果2: 把結果裡的逗號替換成"|"

SQL> select replace(wm_concat(name),‘,‘,‘|‘) from test;REPLACE(WM_CONCAT(NAME),‘,‘,‘|‘)-----------------------------------------------------------------------a|b|c|d|e

 

效果3:按ID分組合并name

SQL> select id,wm_concat(name) name from test group by id;ID NAME---------- ------------------------------1 a,b,c2 d,e

 

懶人擴充用法:

案例:我要寫一個視圖,類似"create or replace view as select 欄位1,...欄位50 from tablename" ,基表有50多個欄位,要是靠手工寫太麻煩了,有沒有什麼簡便的方法? 當然有了,看我如果應用wm_concat來讓這個需求變簡單

SQL> select ‘create or replace view as select ‘|| wm_concat(column_name) || ‘ from dept‘from user_tab_columns where table_name=‘DEPT‘;‘CREATEORREPLACEVIEWASSELECT‘||WM_CONCAT(COLUMN_NAME)||‘FROMDEPT‘--------------------------------------------------------------------------------create or replace view as select DEPTNO,DNAME,LOC from dept 

 

oracle查詢多行資料合併成一行資料