Oracle: Combine multiple results in a subquery into a single comma-separated value
There is an excellent summary of the available string aggregation techniques on Tim Hall's site.
Combine multiple results in a subquery into a single comma-separated value
1. Create the UDF:
CREATE FUNCTION CombineValues
(
@FK_ID INT -- The foreign key from TableA which is used
-- to fetch corresponding records
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @SomeColumnList VARCHAR(8000);
SELECT @SomeColumnList =
COALESCE(@SomeColumnList + ', ', '') + CAST(SomeColumn AS varchar(20))
FROM TableB C
WHERE C.FK_ID = @FK_ID;
RETURN
(
SELECT @SomeColumnList
)
END
2. Use in subquery:
SELECT ID, Name, dbo.CombineValues(FK_ID) FROM TableA
3. If you are using stored procedure you can do like this:
CREATE PROCEDURE GetCombinedValues
@FK_ID int
As
BEGIN
DECLARE @SomeColumnList VARCHAR(800)
SELECT @SomeColumnList =
COALESCE(@SomeColumnList + ', ', '') + CAST(SomeColumn AS varchar(20))
FROM TableB
WHERE FK_ID = @FK_ID
Select *, @SomeColumnList as SelectedIds
FROM
TableA
WHERE
FK_ID = @FK_ID
END
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.
Sub-Query Returns More than one row
Try with this query, maybe will be useful (click here to test the query):
CREATE TABLE TEST1(
ID INT);
CREATE TABLE TEST2(
ID INT,
TXT VARCHAR2(100));
INSERT INTO TEST1 VALUES(1);
INSERT INTO TEST1 VALUES(2);
INSERT INTO TEST1 VALUES(3);
INSERT INTO TEST2 VALUES(1,'A');
INSERT INTO TEST2 VALUES(1,'B');
INSERT INTO TEST2 VALUES(2,'C');
INSERT INTO TEST2 VALUES(3,'A');
INSERT INTO TEST2 VALUES(3,'B');
INSERT INTO TEST2 VALUES(3,'C');
/* HERE IS THE QUERY!!!*/
SELECT A.ID,
(SELECT listagg(B.TXT,',' ) WITHIN GROUP (ORDER BY B.ID)
FROM TEST2 B WHERE B.ID = A.ID
) AS CONTATENATED_FIELD
FROM TEST1 A;
NOTE: listagg works in 11.X versions, please see this link for more information.
According with your query maybe you need something like this:
select o.id,
(SELECT listagg(v.value,',' ) WITHIN GROUP (ORDER BY v.value) from values v join attributes a on v.att_id=a.att_id where a.att_id='100' and v.id=o.id)
from objects o
where o.class_id='GGzX';
SQL - Multiple Values comma separated when using GROUP BY
This link refers to a number of examples of different ways to do this on Oracle. See if there's something there that you have permissions on your database to do.
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;
comma-separated list as a result of select statement in Oracle
SELECT parent_id,
RTRIM(XMLAGG(XMLELEMENT(e,child_id || ',')).EXTRACT('//text()'),',') AS "Children"
FROM parentChildTable
WHERE parent_id = 0
GROUP BY parent_id
or
SELECT parent_id,
LISTAGG(child_id, ',') WITHIN GROUP (ORDER BY child_id) AS "Children"
FROM parentChildTable
WHERE parent_id = 0
GROUP BY parent_id
Oracle - Subquery with concat possible?
this looks like a string aggregation question, see these questions on SO for a description of available methods:
- Oracle: Combine multiple results in a subquery into a single comma-separated value
- Add comma-separated value of grouped rows to existing query
- SQL - Multiple Values comma separated when using GROUP BY
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
Related Topics
Postgres and Indexes on Foreign Keys and Primary Keys
Find Rows That Have the Same Value on a Column in MySQL
How to Exclude Rows That Don't Join with Another Table
What Is Full Text Search VS Like
Select SQL Server Database Size
How to Export All Data from Table to an Insertable SQL Format
How to Do an Inner Join on Multiple Columns
How to List All Tables in a Schema in Oracle SQL
Improve SQL Server Query Performance on Large Tables
Run a Query with a Limit/Offset and Also Get the Total Number of Rows
How to Select Every Row Where Column Value Is Not Distinct
Mysql: Compare Differences Between Two Tables
Find All Records Which Have a Count of an Association Greater Than Zero
Difference Between Full Join & Inner Join
Differencebetween Group by and Order by in SQL
Comparison of Relational Databases and Graph Databases
How to Group by Date Time Column Without Taking Time into Consideration