pavelmed
asked on
In Oracle, how can I concatenate multiple results returned from select statement into a comma-separated string?
In Oracle, how can I concatenate multiple results returned from a single-field select statement into a comma-separated string?
I am trying to use in in a subquery so in case a subquery returns multiple rows, I could concatenate them into a single row result
Thank you!
I am trying to use in in a subquery so in case a subquery returns multiple rows, I could concatenate them into a single row result
Thank you!
select a || ', ' || b || ', ' || c
from table
where foo = bar
from table
where foo = bar
kaijaibe, that will only concatenate multiple columns of a single row.
not single column of multiple rows
not single column of multiple rows
here's an example of stragg and xmlaggregation
https://www.experts-exchange.com/questions/24120361/Simple-Query.html
https://www.experts-exchange.com/questions/24120361/Simple-Query.html
also note, when you upgrade to 11gR2 the LISTAGG function is built in
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The hierarchical query method suggested by slightwv is extremely resource intensive compared to the other methods (of course, I always recommend testing yourself to verify)
The row_number/connect by method is, however, the only method available in version 9i that doesn't require the creation of additional types and/or functions.
The row_number/connect by method is, however, the only method available in version 9i that doesn't require the creation of additional types and/or functions.
There is also a built-in function wm_concat in some versions (I think 10gr2+).
SQL> select ename from emp;
ENAME
---------
tony
Janice
marvin
zeek
SQL> select wm_concat(ename) as employees
2 from emp;
EMPLOYEES
-------------------------- ---------- ------
tony,Janice,marvin,zeek
SQL> select ename from emp;
ENAME
---------
tony
Janice
marvin
zeek
SQL> select wm_concat(ename) as employees
2 from emp;
EMPLOYEES
--------------------------
tony,Janice,marvin,zeek
wm_concat is not documented or supported by oracle.
I think wm_concat is an early attempt at what eventually became listagg in 11gR2
ASKER
I am leaning to a solution by slightwv which he calls "neat trick" - from this link:
https://www.experts-exchange.com/questions/24912161/Relationship-Query-in-SQL.html
(the code from that article is shown below).
However, while it worked fine for the simplified table with just two columns, I have some incorrect result for a table with 3 comumns.
So it works file for the 2 columns returning this
Column1
-------------------------- --------
select stu_id, trim(',' from sys_connect_by_path(class_ name, ',') )
from (
select stu_id, class_name,
row_number() over(partition by stu_id order by class_name) rn,
count(*) over(partition by stu_id) cp
from class_reg
)
where rn = cp
start with rn = 1
connect by prior stu_id = stu_id
and prior rn = rn - 1
https://www.experts-exchange.com/questions/24912161/Relationship-Query-in-SQL.html
(the code from that article is shown below).
However, while it worked fine for the simplified table with just two columns, I have some incorrect result for a table with 3 comumns.
So it works file for the 2 columns returning this
Column1
--------------------------
select stu_id, trim(',' from sys_connect_by_path(class_
from (
select stu_id, class_name,
row_number() over(partition by stu_id order by class_name) rn,
count(*) over(partition by stu_id) cp
from class_reg
)
where rn = cp
start with rn = 1
connect by prior stu_id = stu_id
and prior rn = rn - 1
your example only shows 2 columns, what do you want with your 3 column table?
please give short example of inputs and expected output.
again, if you are on 10g or higher, I recommend testing the xml aggregation vs the hierarchical.
Not only is hierarchical more resource intensive it's also bigger code.
and, if you can create a type and a function, then I HIGHLY recommend using either the stragg or the collect method (stragg is simpler syntax in the long run, but collect is pretty easy too and more efficient)
please give short example of inputs and expected output.
again, if you are on 10g or higher, I recommend testing the xml aggregation vs the hierarchical.
Not only is hierarchical more resource intensive it's also bigger code.
and, if you can create a type and a function, then I HIGHLY recommend using either the stragg or the collect method (stragg is simpler syntax in the long run, but collect is pretty easy too and more efficient)
ASKER
Sorry, sent by accident. I will repeat:
I am leaning to a solution by slightwv which he calls "neat trick" - from this link:
https://www.experts-exchange.com/questions/24912161/Relationship-Query-in-SQL.html
(the code from that article is shown below).
However, while it worked fine for the simplified table with just two columns, I have some incorrect result for a table with 3 comumns.
So it works file for the 2 columns returning this
Column1 Column2
1 BB, CC, PP, DD
But I need to deal with 3 table column, such as
Column1 Column2 Column3
1 11 BB, CC, PP
1 12 PP, CD, HH
2 11 AA, VV, BB
2 12 KK, BB, NN
I tried but could not modernize the code below to work with two prior levels instead of just one level
Could you please suggest?
I am leaning to a solution by slightwv which he calls "neat trick" - from this link:
https://www.experts-exchange.com/questions/24912161/Relationship-Query-in-SQL.html
(the code from that article is shown below).
However, while it worked fine for the simplified table with just two columns, I have some incorrect result for a table with 3 comumns.
So it works file for the 2 columns returning this
Column1 Column2
1 BB, CC, PP, DD
But I need to deal with 3 table column, such as
Column1 Column2 Column3
1 11 BB, CC, PP
1 12 PP, CD, HH
2 11 AA, VV, BB
2 12 KK, BB, NN
I tried but could not modernize the code below to work with two prior levels instead of just one level
Could you please suggest?
what does the input look like that produces this output?
Column1 Column2 Column3
1 11 BB, CC, PP
1 12 PP, CD, HH
2 11 AA, VV, BB
2 12 KK, BB, NN
Column1 Column2 Column3
1 11 BB, CC, PP
1 12 PP, CD, HH
2 11 AA, VV, BB
2 12 KK, BB, NN
I didn't read all the replies, but here is a pipeline function (inside the /* and */ coment. Below that is an example of how you can call it. Below that is another example that puts quotes around each item.
/************************* ********** ********** ********** ********** *********
************************** ********** ********** ********** ********** *********
This query utilized the function 'join' which must exist and is listed below.
************************** ********** ********** ********** ********** ********/
/*
create or replace function join
(
p_cursor sys_refcursor,
p_del varchar2 := ','
) return varchar2
is
l_value varchar2(32767);
l_result varchar2(32767);
begin
loop
fetch p_cursor into l_value;
exit when p_cursor%notfound;
if l_result is not null then
l_result := l_result || p_del;
end if;
l_result := l_result || l_value;
end loop;
return l_result;
end join;
*/
set linesize 2000
set sqlcase upper
set heading off
set verify off
set feedback off
select join(cursor(select decode(rownum,1,'('||COLUM N_NAME,COL UMN_NAME) columns
from user_tab_columns where table_name = '&table_name')) || ')' from dual;
select join(cursor(select decode(rownum,1,'('''||COL UMN_NAME,C OLUMN_NAME )
from user_tab_columns where table_name = '&table_name'), ''',''') || ''')' column_list from dual
set sqlcase mixed
set heading on
set feedback on
set verify o
/*************************
**************************
This query utilized the function 'join' which must exist and is listed below.
**************************
/*
create or replace function join
(
p_cursor sys_refcursor,
p_del varchar2 := ','
) return varchar2
is
l_value varchar2(32767);
l_result varchar2(32767);
begin
loop
fetch p_cursor into l_value;
exit when p_cursor%notfound;
if l_result is not null then
l_result := l_result || p_del;
end if;
l_result := l_result || l_value;
end loop;
return l_result;
end join;
*/
set linesize 2000
set sqlcase upper
set heading off
set verify off
set feedback off
select join(cursor(select decode(rownum,1,'('||COLUM
from user_tab_columns where table_name = '&table_name')) || ')' from dual;
select join(cursor(select decode(rownum,1,'('''||COL
from user_tab_columns where table_name = '&table_name'), ''',''') || ''')' column_list from dual
set sqlcase mixed
set heading on
set feedback on
set verify o
ASKER
The input looks that this:
Column1 Column2 Column3
1 11 BB
1 11 CC
1 11 PP
1 12 PP
1 12 CD
1 12 HH
2 11 AA
2 11 VV
2 11 BB
2 12 KK
2 12 BB
2 12 NN
Column1 Column2 Column3
1 11 BB
1 11 CC
1 11 PP
1 12 PP
1 12 CD
1 12 HH
2 11 AA
2 11 VV
2 11 BB
2 12 KK
2 12 BB
2 12 NN
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
but... if you really want to use the hierarchical version...
SELECT column1, column2, MAX(SUBSTR(SYS_CONNECT_BY_ PATH(colum n3, ','), 2)) x
FROM (SELECT column1,
column2,
column3,
ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY column3) curr,
ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY column3) - 1 prev
FROM yourtable)
START WITH curr = 1
CONNECT BY PRIOR curr = prev AND column1 = PRIOR column1 AND column2 = PRIOR column2
GROUP BY column1, column2
ORDER BY column1, column2;
SELECT column1, column2, MAX(SUBSTR(SYS_CONNECT_BY_
FROM (SELECT column1,
column2,
column3,
ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY column3) curr,
ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY column3) - 1 prev
FROM yourtable)
START WITH curr = 1
CONNECT BY PRIOR curr = prev AND column1 = PRIOR column1 AND column2 = PRIOR column2
GROUP BY column1, column2
ORDER BY column1, column2;
Sorry sdstuder. I concede the point. XMLAGG is by far the cleanest/fastest. I saw stragg and jumped to the Tom Kyte stragg series of functions. I never followed the links to see the xmlagg.
if your data sets are small enough, you might not notice the difference, but the xml version is faster, uses fewer sort steps, less memory, less cpu. So it won't scale well as the data volume grows.
But, I would look into the option of creating a type and function like in the snippet below
makes the query easier to write as well as more efficient
SELECT column1, column2, tbl2str(CAST(COLLECT(colum n3) AS vcarray))
FROM yourtable
GROUP BY column1, column2;
But, I would look into the option of creating a type and function like in the snippet below
makes the query easier to write as well as more efficient
SELECT column1, column2, tbl2str(CAST(COLLECT(colum
FROM yourtable
GROUP BY column1, column2;
CREATE OR REPLACE TYPE VCARRAY AS TABLE OF VARCHAR2(4000);
CREATE OR REPLACE FUNCTION tbl2str(p_tbl IN vcarray, p_delimiter IN VARCHAR2 DEFAULT ',' )
RETURN VARCHAR2
DETERMINISTIC
IS
v_str VARCHAR2(32767);
BEGIN
IF p_tbl.COUNT > 0
THEN
v_str := p_tbl(1);
FOR i IN 2 .. p_tbl.COUNT
LOOP
v_str := v_str || p_delimiter || p_tbl(i);
END LOOP;
END IF;
RETURN v_str;
END;
slightwv,
I still like stragg over the other methods when performance isn't critical.
you just can't beat this for easy, and it's not too bad performance wise either. Not the best, but not the worst
SELECT column1, column2, stragg(column3)
FROM yourtable
GROUP BY column1, column2;
I still like stragg over the other methods when performance isn't critical.
you just can't beat this for easy, and it's not too bad performance wise either. Not the best, but not the worst
SELECT column1, column2, stragg(column3)
FROM yourtable
GROUP BY column1, column2;
ASKER
sdstuber and sliqhtwv,
Thank you for your answers.
I am accepting the XMLAGG solution suggested by sdstuber, but I will also accept the SYS_CONNECT_BY_PATH solution as assisted solution as I worked with both of them and they are very useful.
I'll try to split the points.
Thank you very much!!!
Thank you for your answers.
I am accepting the XMLAGG solution suggested by sdstuber, but I will also accept the SYS_CONNECT_BY_PATH solution as assisted solution as I worked with both of them and they are very useful.
I'll try to split the points.
Thank you very much!!!
ASKER
I also like stragg for its simplicity, but Oracle returned ORA-00904: "STRAGG": invalid identifier
you have to create stragg and it's underlying types (the second link I posted above shows them)
there are a lot of implementations of stragg on the internet
but due to the constraints of the cartridge api, there is very little differentiation between, only minor naming conventions. They should all work fine
there are a lot of implementations of stragg on the internet
but due to the constraints of the cartridge api, there is very little differentiation between, only minor naming conventions. They should all work fine
or you can use the COLLECT built in function and then turn it into a string with tbl2str (search EE for tbl2str)
or you can use xml built in aggregation, here's an example
https://www.experts-exchange.com/questions/24609896/Oracle-SQL-comma-string-comma-needed.html
note, the COLLECT way is the most efficient but does require creation of extra objects