heting

:: 管理 ::

40 隨筆 :: 9 文章 :: 45 評論 :: 0 Trackbacks

create table m_researcher_stock_rel(N_SEC_CODE CHAR(6)

, C_RESEARCHER_CODE varchar(20))

drop table m_researcher_stock_rel

select * from m_researcher_stock_rel

insert into m_researcher_stock_rel values('000297', 'chenpeng');

insert into m_researcher_stock_rel values('000297', 'peopeo');

insert into m_researcher_stock_rel values('000297', 'aaa');

insert into m_researcher_stock_rel values('000297', 'bbb');

insert into m_researcher_stock_rel values('000297', 'ccc');

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”替換為你的表名，就是這么簡單。

posted on 2010-03-03 17:49 賀挺閱讀(2188) 評論(0) 編輯收藏所屬分類: 數據庫


只有注冊用戶登錄后才能發表評論。




網站導航: 博客園 IT新聞 Chat2DB C++博客博問管理
相關文章: oracle 創建帶參視圖 oracle多列轉一行 sql2005中datetime 時間類型的格式化