I have a simple query:
select * from countries
with the following results:
country_name
------------
Albania
Andorra
Antigua
.....
I would like to return the results in one row, so like this:
Albania, Andorra, Antigua, ...
Of course, I can write a PL/SQL function to do the job (I already did in Oracle 10g), but is there a nicer, preferably non-Oracle-specific solution (or may be a built-in function) for this task?
I would generally use it to avoid multiple rows in a sub-query, so if a person has more then one citizenship, I do not want her/him to be a duplicate in the list.
My question is based on the similar question on SQL server 2005.
UPDATE: My function looks like this:
CREATE OR REPLACE FUNCTION APPEND_FIELD (sqlstr in varchar2, sep in varchar2 ) return varchar2 is
ret varchar2(4000) := '';
TYPE cur_typ IS REF CURSOR;
rec cur_typ;
field varchar2(4000);
begin
OPEN rec FOR sqlstr;
LOOP
FETCH rec INTO field;
EXIT WHEN rec%NOTFOUND;
ret := ret || field || sep;
END LOOP;
if length(ret) = 0 then
RETURN '';
else
RETURN substr(ret,1,length(ret)-length(sep));
end if;
end;
This question is related to
sql
oracle
concatenation
pivot
string-aggregation
you can use this query to do the above task
DECLARE @test NVARCHAR(max)
SELECT @test = COALESCE(@test + ',', '') + field2 FROM #test SELECT field2= @test
for detail and step by step explanation visit the following link
http://oops-solution.blogspot.com/2011/11/sql-server-convert-table-column-data.html
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.
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;
you can try this query.
select listagg(country_name,',') within group (order by country_name) cnt
from countries;
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;
I needed a similar thing and found the following solution.
select RTRIM(XMLAGG(XMLELEMENT(e,country_name || ',')).EXTRACT('//text()'),',') country_name from
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.
you can try this query.
select listagg(country_name,',') within group (order by country_name) cnt
from countries;
For Oracle you can use LISTAGG
SELECT REPLACE(REPLACE
((SELECT TOP (100) PERCENT country_name + ', ' AS CountryName
FROM country_name
ORDER BY country_name FOR XML PATH('')),
'&<CountryName>', ''), '&<CountryName>', '') AS CountryNames
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.
In this example we are creating a function to bring a comma delineated list of distinct line level AP invoice hold reasons into one field for header level query:
FUNCTION getHoldReasonsByInvoiceId (p_InvoiceId IN NUMBER) RETURN VARCHAR2
IS
v_HoldReasons VARCHAR2 (1000);
v_Count NUMBER := 0;
CURSOR v_HoldsCusror (p2_InvoiceId IN NUMBER)
IS
SELECT DISTINCT hold_reason
FROM ap.AP_HOLDS_ALL APH
WHERE status_flag NOT IN ('R') AND invoice_id = p2_InvoiceId;
BEGIN
v_HoldReasons := ' ';
FOR rHR IN v_HoldsCusror (p_InvoiceId)
LOOP
v_Count := v_COunt + 1;
IF (v_Count = 1)
THEN
v_HoldReasons := rHR.hold_reason;
ELSE
v_HoldReasons := v_HoldReasons || ', ' || rHR.hold_reason;
END IF;
END LOOP;
RETURN v_HoldReasons;
END;
I have always had to write some PL/SQL for this or I just concatenate a ',' to the field and copy into an editor and remove the CR from the list giving me the single line.
That is,
select country_name||', ' country from countries
A little bit long winded both ways.
If you look at Ask Tom you will see loads of possible solutions but they all revert to type declarations and/or PL/SQL
In this example we are creating a function to bring a comma delineated list of distinct line level AP invoice hold reasons into one field for header level query:
FUNCTION getHoldReasonsByInvoiceId (p_InvoiceId IN NUMBER) RETURN VARCHAR2
IS
v_HoldReasons VARCHAR2 (1000);
v_Count NUMBER := 0;
CURSOR v_HoldsCusror (p2_InvoiceId IN NUMBER)
IS
SELECT DISTINCT hold_reason
FROM ap.AP_HOLDS_ALL APH
WHERE status_flag NOT IN ('R') AND invoice_id = p2_InvoiceId;
BEGIN
v_HoldReasons := ' ';
FOR rHR IN v_HoldsCusror (p_InvoiceId)
LOOP
v_Count := v_COunt + 1;
IF (v_Count = 1)
THEN
v_HoldReasons := rHR.hold_reason;
ELSE
v_HoldReasons := v_HoldReasons || ', ' || rHR.hold_reason;
END IF;
END LOOP;
RETURN v_HoldReasons;
END;
For Oracle you can use LISTAGG
You can use this as well:
SELECT RTRIM (
XMLAGG (XMLELEMENT (e, country_name || ',')).EXTRACT ('//text()'),
',')
country_name
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.
you can use this query to do the above task
DECLARE @test NVARCHAR(max)
SELECT @test = COALESCE(@test + ',', '') + field2 FROM #test SELECT field2= @test
for detail and step by step explanation visit the following link
http://oops-solution.blogspot.com/2011/11/sql-server-convert-table-column-data.html
I have always had to write some PL/SQL for this or I just concatenate a ',' to the field and copy into an editor and remove the CR from the list giving me the single line.
That is,
select country_name||', ' country from countries
A little bit long winded both ways.
If you look at Ask Tom you will see loads of possible solutions but they all revert to type declarations and/or PL/SQL
Source: Stackoverflow.com