SQL Help: Select statement Concatenate a One to Many relationship
Assuming you're using SQL Server 2005:
This should do what you're after - obviously replace fields as you need:
For demo purposes, consider the following two table structures:
Students(
STU_PKEY Int Identity(1,1) Constraint PK_Students_StuPKey Primary Key,
STU_NAME nvarchar(64)
)
Courses(
CRS_PKEY Int Identity(1, 1) Constraint PK_Courses_CrsPKey Primary Key,
STU_KEY Int Constraint FK_Students_StuPKey Foreign Key References Students(STU_PKEY),
CRS_NAME nvarchar(64)
)
Now this query should do the job you're after:
Select s.STU_PKEY, s.STU_NAME As Student,
Stuff((
Select ',' + c.CRS_NAME
From Courses c
Where s.STU_PKEY = c.STU_KEY
For XML Path('')
), 1, 1, '') As Courses
From Students s
Group By s.STU_PKEY, s.STU_NAME
Way simpler than the currently accepted answer...
One-to-Many SQL SELECT concatenated into single row
You are almost there - you just need aggregation:
SELECT
o.id,
o.status,
STRING_AGG(c.text, ',') comments
FROM "Order" o
LEFT JOIN "Comment" c ON p.id = c."order"
GROUP BY o.id, o.status
I would strongly recommend against having a table (and/or a column) called order
: because it conflicts with a language keyword. I would also recommend avoiding quoted identifiers as much as possible - they make the queries longer to write, for no benefit.
Note that you can also use a correlated subquery:
SELECT
o.id,
o.status,
(SELECT STRING_AGG(c.text, ',') FROM "Comment" c WHERE c."order" = p.id) comments
FROM "Order" o
how to concatenate one to many records in sql server
You have to use Below 2 SQL Function
XML Path- For Concatenation
Stuff For Comma separation
select UserId,
stuff((select ',' + t2.Rolename
from UserRoles t2 where t1.UserId = t2.UserId
for xml path('')),1,1,'') Roles
from UserRoles t1
group by UserId
SQL Fiddle
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
Show a one to many relationship as 2 columns - 1 unique row (ID & comma separated list)
I believe that the answer you need is a user-defined aggregate, similar to this one:
CREATE FUNCTION gc_init(dummy VARCHAR(255)) RETURNING LVARCHAR;
RETURN '';
END FUNCTION;
CREATE FUNCTION gc_iter(result LVARCHAR, value VARCHAR(255))
RETURNING LVARCHAR;
IF result = '' THEN
RETURN TRIM(value);
ELSE
RETURN result || ',' || TRIM(value);
END IF;
END FUNCTION;
CREATE FUNCTION gc_comb(partial1 LVARCHAR, partial2 LVARCHAR)
RETURNING LVARCHAR;
IF partial1 IS NULL OR partial1 = '' THEN
RETURN partial2;
ELIF partial2 IS NULL OR partial2 = '' THEN
RETURN partial1;
ELSE
RETURN partial1 || ',' || partial2;
END IF;
END FUNCTION;
CREATE FUNCTION gc_fini(final LVARCHAR) RETURNING LVARCHAR;
RETURN final;
END FUNCTION;
CREATE AGGREGATE group_concat
WITH (INIT = gc_init, ITER = gc_iter,
COMBINE = gc_comb, FINAL = gc_fini);
Given a table of elements (called elements) with a column called name containing (funnily enough) the element name, and another column called atomic_number, this query produces this result:
SELECT group_concat(name) FROM elements WHERE atomic_number < 10;
Hydrogen,Helium,Lithium,Beryllium,Boron,Carbon,Nitrogen,Oxygen,Fluorine
Applied to the question, you should obtain the answer you need from:
SELECT id, group_concat(codes)
FROM anonymous_table
GROUP BY id;
CREATE TEMP TABLE anonymous_table
(
id INTEGER NOT NULL,
codes CHAR(4) NOT NULL,
PRIMARY KEY (id, codes)
);
INSERT INTO anonymous_table VALUES(63592, 'PELL');
INSERT INTO anonymous_table VALUES(58640, 'SUBL');
INSERT INTO anonymous_table VALUES(58640, 'USBL');
INSERT INTO anonymous_table VALUES(73571, 'PELL');
INSERT INTO anonymous_table VALUES(73571, 'USBL');
INSERT INTO anonymous_table VALUES(73571, 'SUBL');
INSERT INTO anonymous_table VALUES(73572, 'USBL');
INSERT INTO anonymous_table VALUES(73572, 'PELL');
INSERT INTO anonymous_table VALUES(73572, 'SUBL');
SELECT id, group_concat(codes)
FROM anonymous_table
GROUP BY id
ORDER BY id;
The output from that is:
58640 SUBL,USBL
63592 PELL
73571 PELL,SUBL,USBL
73572 PELL,SUBL,USBL
The extra set of data was added to test whether insert sequence affected the result; it appears not to do so (the codes are in sorted order; I'm not sure whether there's a way to alter - reverse - that order).
Notes:
- This aggregate should be usable for any type that can be converted to VARCHAR(255), which means any numeric or temporal type. Long CHAR columns and blob types (BYTE, TEXT, BLOB, CLOB) are not handled.
- The plain LVARCHAR limits the aggregate size to 2048 bytes. If you think you need longer lengths, specify
LVARCHAR(10240)
(for 10 KiB), for example. - As of Informix 12.10.FC5, the maximum length that works seems to be 16380; anything longer seems to trigger
SQL -528: Maximum output rowsize (32767) exceeded
, which surprises me. If you need to remove the aggregate, you can use:
DROP AGGREGATE IF EXISTS group_concat;
DROP FUNCTION IF EXISTS gc_fini;
DROP FUNCTION IF EXISTS gc_init;
DROP FUNCTION IF EXISTS gc_iter;
DROP FUNCTION IF EXISTS gc_comb;
Concatenate several fields into one with SQL
Sergio del Amo:
However, I am not getting the pages without tags. I guess i need to write my query with left outer joins.
SELECT pagetag.id, page.name, group_concat(tag.name)
FROM
(
page LEFT JOIN pagetag ON page.id = pagetag.pageid
)
LEFT JOIN tag ON pagetag.tagid = tag.id
GROUP BY page.id;
Not a very pretty query, but should give you what you want - pagetag.id
and group_concat(tag.name)
will be null
for page 4 in the example you've posted above, but the page shall appear in the results.
Related Topics
How to Find Tables Which Reference a Particular Row via a Foreign Key
Postgres Unique Multi-Column Index for Join Table
How to Use Oracle Outer Join with a Filter Where Clause
Sql - Query to Insert a Column Value If It Does Not Exist in That Column
Which Oracle Table Uses a Sequence
T-Sql: Comparing Two Tables - Records That Don't Exist in Second Table
Row Locks - Manually Using Them
Hql Join - Path Expected for Join! Hibernate
Sql Query to Convert Columns into Rows
The Multi-Part Identifier Could Not Be Bound - Subquery
Is Using "Not Exists" Considered to Be Bad SQL Practise
Presto Sql: Changing Time Zones Using Time Zone String Coming as a Result of a Query Is Not Working
Sql Server 2012: Add a Linked Server to Postgresql
Making Ssdt Just Generate a SQL Script (And Not Deploy a Database)
Is Array All Nulls in Postgresql
Does SQL Server 2008 Support The Create Assertion Syntax
How to Insert into a Table with Just One Identity Column (Sql Express)