How can I concatinate a subquery result field into the parent query?

Question

DB: Sql Server 2008.

I have a really (fake) groovy query like this:-

SELECT CarId, NumberPlate
    (SELECT Owner
     FROM Owners b
     WHERE b.CarId = a.CarId) AS Owners
FROM Cars a
ORDER BY NumberPlate

And this is what I'm trying to get...

=> 1    ABC123     John, Jill, Jane
=> 2    XYZ123     Fred
=> 3    SOHOT      Jon Skeet, ScottGu

So, i tried using

AS [Text()] ... FOR XML PATH('') but that was inlcuding weird encoded characters (eg. carriage return). ... so i'm not 100% happy with that.

I also tried to see if there's a COALESCE solution, but all my attempts failed.

So - any suggestions?

score 7 · Answer 1 · answered Apr 24 '19 at 03:14

Answering an old post, just thought it needed an update for newer versions of SQL Server:

For SQL Server 2017 use STRING_AGG(expression, seperator)

GROUP_CONCAT is MySQL.

Prior to SQL 2017, you can also do something like (snipped from our current code base on SQL Server 2016):

SELECT CarId, NumberPlate,
    (STUFF(( SELECT ', ' + b.Owner
        FROM Owners b
        WHERE b.CarId = a.CarId
        FOR XML PATH('')
        )
        ,1,2,'') AS Owners
FROM Cars a
ORDER BY NumberPlate

Links to STRING_AGG https://database.guide/the-sql-server-equivalent-to-group_concat/ https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017

Link to STUFF: https://docs.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql?view=sql-server-2017

and finally links to FOR XML: https://docs.microsoft.com/en-us/sql/relational-databases/xml/for-xml-sql-server?view=sql-server-2017

score 3 · Accepted Answer · edited May 23 '17 at 10:30

3

Try the solution to this question:

How to create a SQL Server function to "join" multiple rows from a subquery into a single delimited field?

:)

edited May 23 '17 at 10:30

Community

1
1

answered Jun 19 '09 at 07:35

Jeremy Smyth

22,724
2
50
65

Ha -> that post was also using cars as an example :) I ended up creating a temp UDF, run my code, drop udf. Works great :) This is for an import process .. hence the create/drop of the udf. cheers :) – Pure.Krome Jun 19 '09 at 08:01

score 0 · Answer 3 · answered Mar 23 '19 at 13:10

0

Use GROUP_CONCAT

SELECT CarId, NumberPlate
    (SELECT GROUP_CONCAT(Owner)
     FROM Owners b
     WHERE b.CarId = a.CarId) AS Owners
    FROM Cars a
ORDER BY NumberPlate

answered Mar 23 '19 at 13:10

Mohamed hesham

106
1
11

2

GROUP_CONCAT doesn't exist in Sql Server 2008. Or any version, for that matter. – stevieg Aug 13 '19 at 03:46

How can I concatinate a subquery result field into the parent query?

3 Answers3