的毗连第二列值，如果第一列的值是相同

Question

我有像下面的查询，并且列出的输出的它：

SELECT DISTINCT TRACKING_NUM,TITLE_OF_DOC_SEC 
FROM some_table 
WHERE TRACKING_NUM IS NOT NULL; 

O/P：

TRACKING_NUM TITLE_OF_DOC_SEC 
007   Email Flow 
007   Test Bug 53306 
007   Title 1119 
007   Title Test 
007   test bug 
009   1156 
089   Title 21173 
098   test Doc Section 

我想重新写查询，以便得到一个输出是这样的：

TRACKING_NUM TITLE_OF_DOC_SEC 
007    Email Flow,Test Bug 53306,Title 1119,Title Test,test bug 
009    1156 
089    Title 21173 
098    test Doc Section 

任何人都可以帮忙吗？

Answer 1

使用LISTAGG（）在11g或WM_Concat（）在10g中：

SELECT LISTAGG(TITLE_OF_DOC_SEC, ',') WITHIN GROUP (ORDER BY TRACKING_NUM) AS TITLE_OF_DOC_SEC 
    FROM your table 
    WHERE.... 

    SELECT WM_CONCAT(TITLE_OF_DOC_SEC) AS TITLE_OF_DOC_SEC 
    FROM your table 
    WHERE.... 

Answer 2

如果您正在使用Oracle 11g +，那么你可以使用LISTAGG()：

SELECT TRACKING_NUM, 
    LISTAGG(TITLE_OF_DOC_SEC, ', ') WITHIN GROUP (ORDER BY TRACKING_NUM) AS TITLE_OF_DOC_SEC 
FROM some_table 
WHERE TRACKING_NUM IS NOT NULL 
GROUP BY TRACKING_NUM; 

见SQL Fiddle with Demo

Answer 3

在11g Listagg中是最好的选择：

SQL> select tracking_num, 
    2   listagg(title_of_doc_sec,',') within group (order by title_of_doc_sec) title_of_doc_sec 
    3 from (select distinct tracking_num , title_of_doc_sec from some_table) 
    4 group by tracking_num; 

TRA TITLE_OF_DOC_SEC 
--- ---------------------------------------------------------------------- 
007 Email Flow,Test Bug 53306,Title 1119,Title Test,test bug 
009 1156 
089 Title 21173 
098 test Doc Section 

10g将可以使用wm_concat（但要知道，这是没有记录）：

SQL> select tracking_num, 
    2   wm_concat(title_of_doc_sec) title_of_doc_sec 
    3 from (select distinct tracking_num , title_of_doc_sec from some_table) 
    4 group by tracking_num; 

TRA TITLE_OF_DOC_SEC 
--- ---------------------------------------------------------------------- 
007 Title Test,test bug,Title 1119,Email Flow,Test Bug 53306 
009 1156 
089 Title 21173 
098 test Doc Section 

甚至示范条款：

SQL> with data as (select distinct tracking_num , title_of_doc_sec from some_table) 
    2 select tracking_num, title_of_doc_sec 
    3 from (select * 
    4   from data 
    5   model 
    6   partition by (tracking_num) 
    7   dimension by (row_number() over (partition by tracking_num order by title_of_doc_sec) rn) 
    8   measures (title_of_doc_sec t, cast(null as varchar2(4000)) title_of_doc_sec, 
    9     count(*) over (partition by tracking_num) cnt) 
10   rules(
11   title_of_doc_sec[any] = case when t[cv() - 1 ] is null 
12          then t[cv()] 
13          else title_of_doc_sec[cv()-1]||', '|| t[cv()] 
14          end 
15   )) 
16 where cnt = rn; 

TRA TITLE_OF_DOC_SEC 
--- ---------------------------------------------------------------------- 
007 Email Flow, Test Bug 53306, Title 1119, Title Test, test bug 
009 1156 
089 Title 21173 
098 test Doc Section