Соединение строк в одну ячейку через разделитель

Соединение строк в одну ячейку через разделитель

with test_asg as (
select 100 ASSIGNMENT_ID, 'Comp info' TITLE, 'AP' EMP_CAT from dual union all		
select 	100,'Technician','AP'	 from dual union all
select 	101,'Faculty','AP'	 from dual union all
select 	102,'Teaching','CS'	 from dual union all
select 	102,'Grad Assist','CS'	 from dual union all
select 	102,'Secretary','AP'	 from dual union all
select 	103,'Director','AP'	 from dual 
)
select assignment_id, substr(asg_title,2) JOB_TITLE, substr(asg_empcat,2) EMP_CATEGORY
from (
select assignment_id, max(sys_connect_by_path(title,';')) asg_title, max(sys_connect_by_path(emp_cat,';')) asg_empcat
from (
select assignment_id, title, emp_cat, row_number() over (partition by assignment_id order by assignment_id) rnum
from test_asg
)
start with rnum = 1
connect by prior rnum = rnum-1 and prior assignment_id = assignment_id
group by assignment_id
order by assignment_id
)
order by assignment_id;

результат:

ASSIGNMENT_ID  JOB_TITLE                       EMP_CATEGORY   
-----------------------------------------------------------
100            Comp info;Technician            AP;AP          
101            Faculty                         AP             
102            Teaching;Grad Assist;Secretary  CS;CS;AP       
103            Director                        AP             

источник:

http://www.blog.arun-prabha.com/2009/02/23/oracle-sql-converting-multiple-rows-into-single-row/


вопрос: есть способы делать то же самое эффективнее?

Alexus12,

есть. RTFM ТОП14. XMLAGG, LISTAGG, STRAGG, WM_CONCAT...

Есть.
В десятке - wm_concat
В девятке - поищите статью Бегуна в Oracle Magazine RE про ODCI

нужно в десятке

вижу такое:
/topic/184967
xml рулит,А извраты с sys_connect_by_path обламываются уже на 150 записях...

select \nsys_xmlagg(xmlelement(col, OBJECT_NAME||\',\')).extract(\'/ROWSET/COL/text()\').getclobval()  \nfrom all_objects \nwhere rownum < 10000;

это лучший вариант?

описание вариантов: «Oracle SQL: displaying multiple column values per row»
http://www.dba-oracle.com/t_display_multiple_column_values_same_rows.htm

- Download data into Excel Pivot tables
- The Oracle 9i xmlagg function
- Using the 11g within group SQL syntax
- Write a PL/SQL function
- Use the SYS_CONNECT_BY_PATH operator
- Use Oracle Cross Join

Alexus12

www.dba-oracle.com

Здесь это моветон. И весьма справедливо

Может быть в 10-й версии быстрее будет с использованием новой функции collect?
См. http://www.oracle-developer.net/display.php?id=306

спасибо, к сожалению, требуется решить задачу без создания объектов, только SQL/встроенные фукнции

против sys_xmlagg / за другие способы есть аргументы?
набор на вход - тысячи строк

Alexus12,

Если количество строк ограничено (а куда ж ему деваться, если результат varchar2), то можно сделать через model.

No need to group by and multiple order by:

with test_asg as (
                  select 100 ASSIGNMENT_ID, 'Comp info' TITLE, 'AP' EMP_CAT from dual union all		
                  select 	100,'Technician','AP'	 from dual union all
                  select 	101,'Faculty','AP'	 from dual union all
                  select 	102,'Teaching','CS'	 from dual union all
                  select 	102,'Grad Assist','CS'	 from dual union all
                  select 	102,'Secretary','AP'	 from dual union all
                  select 	103,'Director','AP'	 from dual 
                 )
select  assignment_id,
        substr(sys_connect_by_path(title,';'),2) JOB_TITLE,
        substr(sys_connect_by_path(emp_cat,';'),2) EMP_CATEGORY
  from  (
         select  assignment_id,
                 title,
                 emp_cat,
                 row_number() over (partition by assignment_id order by assignment_id) rnum
           from  test_asg
        )
  where connect_by_isleaf = 1
  start with rnum = 1
  connect by assignment_id = prior assignment_id
         and rnum = prior rnum + 1
  order by assignment_id
/

ASSIGNMENT_ID JOB_TITLE                                EMP_CATEGORY
------------- ---------------------------------------- ------------------------------
          100 Comp info;Technician                     AP;AP
          101 Faculty                                  AP
          102 Teaching;Grad Assist;Secretary           CS;CS;AP
          103 Director                                 AP

SQL> 

SY.