Concat el valor de la segunda columna si el valor de la primera columna es el mismo

Tengo una consulta como a continuación y la salida que aparece en la list:

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 

Quiero volver a escribir la consulta para que la salida sea así:

 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 

¿Alguien puede ayudar?

Use Listagg () en 11g o WM_Concat () en 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.... 

Si está utilizando Oracle 11g +, puede usar 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; 

Ver SQL Fiddle con demostración

en 11g Listagg es la mejor opción:

 SQL> select tracking_num, 2 listgg(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 puede usar wm_concat (pero tenga en count que esto no está documentado):

 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 

o incluso la cláusula model:

 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