SQL Query to concatenate column values from multiple rows in Oracle
There are a few ways depending on what version you have - see the oracle documentation on string aggregation techniques. A very common one is to use LISTAGG
:
SELECT pid, LISTAGG(Desc, ' ') WITHIN GROUP (ORDER BY seq) AS description
FROM B GROUP BY pid;
Then join to A
to pick out the pids
you want.
Note: Out of the box, LISTAGG
only works correctly with VARCHAR2
columns.
concatenate column values from multiple rows in Oracle without duplicates
One option would be using regexp_replace()
:
select regexp_replace(
listagg( t.id,',') within group (order by t.id)
, '([^,]+)(,\1)+', '\1') as "Result"
from t
Demo
Oracle - SQL to concatenate multiple rows
If you have Oracle 11g you could use the LISTAGG() function for this:
SELECT
id
, listagg(str) WITHIN GROUP (ORDER BY OFFSET) AS str_of_str
FROM yourtable
GROUP BY id
see: http://docs.oracle.com/cd/E11882_01/server.112/e10592/functions089.htm
and this sqlfiddle
| ID | STR_OF_STR |
|----|--------------------------|
| 1 | TestSTR1TestSTR3TestSTR5 |
| 2 | TestSTR4TestSTR2 |
| 3 | TestSTR6 |
Concatenate across columns and rows using group and listagg in Oracle SQL
Use the following query where you will directly get concatenated column values:
SELECT
"GROUP",
LISTAGG(VAL_1 || VAL_2 || VAL_3)
WITHIN GROUP(ORDER BY VAL_1) AS "TEXT"
FROM DATA
GROUP BY "GROUP";
Note: Do not use oracle reserved keywords as the column names. Here GROUP
is the oracle reserved keyword.
Cheers!!
How can multiple rows be concatenated into one in Oracle without creating a stored procedure?
There are many way to do the string aggregation, but the easiest is a user defined function. Try this for a way that does not require a function. As a note, there is no simple way without the function.
This is the shortest route without a custom function: (it uses the ROW_NUMBER() and SYS_CONNECT_BY_PATH functions )
SELECT questionid,
LTRIM(MAX(SYS_CONNECT_BY_PATH(elementid,','))
KEEP (DENSE_RANK LAST ORDER BY curr),',') AS elements
FROM (SELECT questionid,
elementid,
ROW_NUMBER() OVER (PARTITION BY questionid ORDER BY elementid) AS curr,
ROW_NUMBER() OVER (PARTITION BY questionid ORDER BY elementid) -1 AS prev
FROM emp)
GROUP BY questionid
CONNECT BY prev = PRIOR curr AND questionid = PRIOR questionid
START WITH curr = 1;
Concatenate and group multiple rows in Oracle
Consider using LISTAGG function in case you're on 11g:
select grp, listagg(name,',') within group( order by name )
from name_table group by grp
sqlFiddle
upd: In case you're not, consider using analytics:
select grp,
ltrim(max(sys_connect_by_path
(name, ',' )), ',')
scbp
from (select name, grp,
row_number() over
(partition by grp
order by name) rn
from tab
)
start with rn = 1
connect by prior rn = rn-1
and prior grp = grp
group by grp
order by grp
sqlFiddle
How can I combine multiple rows into a comma-delimited list in Oracle?
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.
How to concatenate text from multiple rows into a single text string in SQL Server
If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.
I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH
method can handle the concatenation of the rows very easily.
If there is a table called STUDENTS
SubjectID StudentName
---------- -------------
1 Mary
1 John
1 Sam
2 Alaina
2 Edward
Result I expected was:
SubjectID StudentName
---------- -------------
1 Mary, John, Sam
2 Alaina, Edward
I used the following T-SQL
:
SELECT Main.SubjectID,
LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
(
SELECT DISTINCT ST2.SubjectID,
(
SELECT ST1.StudentName + ',' AS [text()]
FROM dbo.Students ST1
WHERE ST1.SubjectID = ST2.SubjectID
ORDER BY ST1.SubjectID
FOR XML PATH (''), TYPE
).value('text()[1]','nvarchar(max)') [Students]
FROM dbo.Students ST2
) [Main]
You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring
to skip the first one so you don't need to do a sub-query:
SELECT DISTINCT ST2.SubjectID,
SUBSTRING(
(
SELECT ','+ST1.StudentName AS [text()]
FROM dbo.Students ST1
WHERE ST1.SubjectID = ST2.SubjectID
ORDER BY ST1.SubjectID
FOR XML PATH (''), TYPE
).value('text()[1]','nvarchar(max)'), 2, 1000) [Students]
FROM dbo.Students ST2
How to group and concatenate results from multiple records?
You are looking for listagg()
:
select a.id, listagg(b.value, ',') within group (order by b.value)
from a left join
b
on a.name = b.name
where a.id = 12345
group by a.id;
Related Topics
Optimize Group by Query to Retrieve Latest Row Per User
Managing and Debugging SQL Queries in Ms Access
How to Comma Delimit Multiple Rows into One Column
How to List All Foreign Keys Referencing a Given Table in SQL Server
MySQL Insert Query Doesn't Work With Where Clause
What This Query Does to Create Comma Delimited List SQL Server
Listagg Function: "Result of String Concatenation Is Too Long"
How to Insert a Line Break in a SQL Server Varchar/Nvarchar String
Calculate Age in MySQL (Innodb)
Get Top N Records For Each Group of Grouped Results
MySQL How to Fill Missing Dates in Range
Inserting Multiple Rows in a Single SQL Query
Is There a Combination of "Like" and "In" in Sql
Oracle: How to Upsert (Update or Insert into a Table)
How to Split the Name String in MySQL
Group by Clause in MySQL and Postgresql, Why the Error in Postgresql