?
如果是oracle 10g以上 一個(gè)wm_concat函數(shù)就可以解決。
?
如果是oracle 9i以下就有點(diǎn)麻煩了。
?
表結(jié)構(gòu)如下:
NAME Null Type
------------------------ --------- -----
N_SEC_CODE NOT NULL CHAR(6)
C_RESEARCHER_CODE NOT NULL VARCHAR2(20)
此表保存了“股票”與“研究員”的對(duì)應(yīng)關(guān)系數(shù)據(jù),一般而言,對(duì)于同一只股票而言,可能有多個(gè)研究員
對(duì)其進(jìn)行跟蹤研究。所以目前遇到一個(gè)要求,需查詢出每只股票和對(duì)應(yīng)的研究員(研究員代碼間,使用逗號(hào)分隔)。
例如有如下數(shù)據(jù):
000297 chenpeng
000297 liusu
合并處理后需顯示為:
000297 chenpeng,liusu
網(wǎng)上查了很多方法,但通常而言都是編寫自定義多行文本合并函數(shù),或者對(duì)支持的列數(shù)具有局限性。
最后在英文google中搜到如下比較巧的方法。不用在數(shù)據(jù)庫中增加function,純SQL一次性搞定,
而且擴(kuò)充性很好,沒有列的限制。
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;
預(yù)想的結(jié)果成功出現(xiàn),多行數(shù)據(jù)成功匯總到一行,特此分享與大家。對(duì)于你自己的應(yīng)用中,只需要把SQL中“n_sec_code”
換為你的用來匯總的列,“c_researcher_code”替換為需合并文本的列,“m_researcher_stock_rel”替換為你的表名,就是這么簡(jiǎn)單。
SQL分析:
1、利用 “ROW_NUMBER () OVER (PARTITION BY……” 為按“股票代碼”匯總后的數(shù)據(jù)行添加組內(nèi)序號(hào)
2、“SYS_CONNECT_BY_PATH” 按組內(nèi)序號(hào)相鄰關(guān)系,為每一層進(jìn)行不同行的“研究員代碼”疊加
3、再次利用“股票代碼”進(jìn)行組內(nèi)分組,但按第二部中的層次排倒序,增加調(diào)整后等級(jí)
4、取所有調(diào)整后等級(jí)為1的結(jié)果,即為所要求的數(shù)據(jù)行
方法很巧妙,值得學(xué)習(xí)。:-)
感謝網(wǎng)友@OctoberOne、@ericqliu的指點(diǎn),在ORACLE10中可使用以下方法:
SELECT n_sec_code, wmsys.wm_concat (c_researcher_code) as result
FROM m_researcher_stock_rel
GROUP BY n_sec_code
?
又一個(gè)示例:
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函數(shù),用于列轉(zhuǎn)行,逗號(hào)分隔
?
?
首先讓我們來看看這個(gè)神奇的函數(shù) wm_concat (列名),該函數(shù)可以把列值以","號(hào)分隔起來,并顯示成一行,接下來上例子,看看這個(gè)神奇的函數(shù)如何應(yīng)用
準(zhǔn)備測(cè)試數(shù)據(jù)
?
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 : 行轉(zhuǎn)列
?
SQL> select wm_concat(name) from test;
WM_CONCAT(NAME)
-------------------------------------------------------------------------
a,b,c,d,e
?
效果2: 把結(jié)果里的逗號(hào)替換成"|"
?
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
?
懶人擴(kuò)展用法:
案例:我要寫一個(gè)視圖,類似"create or replace view as select 字段1,...字段50 from tablename" ,基表有50多個(gè)字段,要是靠手工寫太麻煩了,有沒有什么簡(jiǎn)便的方法? 當(dāng)然有了,看我如果應(yīng)用wm_concat來讓這個(gè)需求變簡(jiǎn)單
?
本文原路徑: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
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號(hào)聯(lián)系: 360901061
您的支持是博主寫作最大的動(dòng)力,如果您喜歡我的文章,感覺我的文章對(duì)您有幫助,請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點(diǎn)擊下面給點(diǎn)支持吧,站長(zhǎng)非常感激您!手機(jī)微信長(zhǎng)按不能支付解決辦法:請(qǐng)將微信支付二維碼保存到相冊(cè),切換到微信,然后點(diǎn)擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
