You can concatenate rows into single string using COALESCE method. This COALESCE method can be used in SQL Server version 2008 and higher. All you have to do is, declare a varchar variable and inside the coalesce, concat the variable with comma and the column, then assign the COALESCE to the variable.
Oracle Listagg function helps to concatenate multiple rows into a single string.
The WM_CONCAT
function (if included in your database, pre Oracle 11.2) or LISTAGG
(starting Oracle 11.2) should do the trick nicely. For example, this gets a comma-delimited list of the table names in your schema:
select listagg(table_name, ', ') within group (order by table_name)
from user_tables;
or
select wm_concat(table_name)
from user_tables;
More details/options
Link to documentation
Here is a simple way without stragg or creating a function.
create table countries ( country_name varchar2 (100));
insert into countries values ('Albania');
insert into countries values ('Andorra');
insert into countries values ('Antigua');
SELECT SUBSTR (SYS_CONNECT_BY_PATH (country_name , ','), 2) csv
FROM (SELECT country_name , ROW_NUMBER () OVER (ORDER BY country_name ) rn,
COUNT (*) OVER () cnt
FROM countries)
WHERE rn = cnt
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1;
CSV
--------------------------
Albania,Andorra,Antigua
1 row selected.
As others have mentioned, if you are on 11g R2 or greater, you can now use listagg which is much simpler.
select listagg(country_name,', ') within group(order by country_name) csv
from countries;
CSV
--------------------------
Albania, Andorra, Antigua
1 row selected.
You can use this as well:
SELECT RTRIM (
XMLAGG (XMLELEMENT (e, country_name || ',')).EXTRACT ('//text()'),
',')
country_name
FROM countries;
For Oracle you can use LISTAGG
you can try this query.
select listagg(country_name,',') within group (order by country_name) cnt
from countries;
The fastest way it is to use the Oracle collect function.
You can also do this:
select *
2 from (
3 select deptno,
4 case when row_number() over (partition by deptno order by ename)=1
5 then stragg(ename) over
6 (partition by deptno
7 order by ename
8 rows between unbounded preceding
9 and unbounded following)
10 end enames
11 from emp
12 )
13 where enames is not null
Visit the site ask tom and search on 'stragg' or 'string concatenation' . Lots of examples. There is also a not-documented oracle function to achieve your needs.
I needed a similar thing and found the following solution.
select RTRIM(XMLAGG(XMLELEMENT(e,country_name || ',')).EXTRACT('//text()'),',') country_name from
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With