?

如果是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一次性搞定，

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

Code

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 = 1

ORDER 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的結果，即為所要求的數據行

方法很巧妙，值得學習。:-)

感謝網友@OctoberOne、@ericqliu的指點，在ORACLE10中可使用以下方法：

SELECT n_sec_code, wmsys.wm_concat (c_researcher_code) as result

FROM m_researcher_stock_rel

GROUP BY n_sec_code

?

又一個示例：

WITH tab AS(
select '01' no,'ss' name, 10 jiner,'麥子' you from dual union all
select '02' no,'dd' name, 11 jiner,'蘿卜' you from dual union all
select '03' no,'cc' name, 9 jiner,'大豆' you from dual union all
select '01' no,'ss' name, 10 jiner,'白菜' you from dual)
SELECT a.no, a.name, a.jiner, ltrim(MAX(sys_connect_by_path(you, ',')), ',') you
FROM (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) a
START WITH rn = 1
CONNECT BY PRIOR rn = rn - 1 AND
a.no = PRIOR a.no AND
a.name = PRIOR a.name AND
a.jiner = PRIOR a.jiner
GROUP 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,c

2 d,e

?

懶人擴展用法:

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

?

本文原路徑:http://g.kehou.com/t1032773272.html

?

?

?

?

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