sql server sub query with a comma separated resultset

Question

I need to return records on a table and my result set needs to contain a comma separated list.

I have attached an image of the 3 tables. I need to do a select that returns the record in the first table and include the last of AwardFocusName that exist in the 3rd table in the screenshot.

So my result set would return one record and include the list of AwardFocusNames in it (comma separated).

enter image description here

Table names are Nomination, AwardFocus, NominationAwardFocus (in the order they appear in the screenshot). — obautista, Nov 28 '11 at 21:21
Duplicate: [Comma separated results in SQL](https://stackoverflow.com/questions/18870326/comma-separated-results-in-sql) — Gert Arnold, Nov 09 '21 at 08:00

score 66 · Accepted Answer · edited Dec 12 '18 at 07:32

66

Here's a trick I've used in the past to do similar things. Use SUBSTRING function.


    SELECT n.nominationID
        , SUBSTRING((
                            SELECT ',' + naf.awardFocusName
                            FROM NominationAwardFocus naf
                            JOIN AwardFocus af
                                ON naf.awardFocusID = af.awardFocusID
                            WHERE n.nominationID = naf.nominationID
                            FOR XML PATH('')

                        ), 2, 1000000)
    FROM Nomination n

Note that the 2 is used to chop off the leading comma that the subselect adds to the first item, and 1000000 is chosen as a large number to mean "all of the rest of the string".

edited Dec 12 '18 at 07:32

Matthew

1,630
1
14
19

answered Nov 28 '11 at 21:24

Alex

744
5
7

1

You should try this with an `awardFocusName` containing an `&`. It's not pretty. – Mikael Eriksson Nov 28 '11 at 21:41
I did run into the & issue, but a simple REPLACE solved the problem. – Hagelt18 Dec 20 '13 at 17:55
I wish I can vote multiple times for this answer.. You save my day sir, Thank you :) – sohaiby Apr 20 '15 at 06:05
1

Thanks! I wish there was a function like the GROUP_CONCAT in MySQL – Naner Dec 21 '16 at 16:04
See here for a similar solution using the STUFF function https://stackoverflow.com/a/31212160/8595398 – Matthew Dec 12 '18 at 04:41

score 2 · Answer 2 · answered Nov 28 '11 at 21:37

Create a Scalar-valued Function like this

CREATE FUNCTION [dbo].[CreateCSV](
    @Id AS INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    Declare @lst varchar(max)

    select @lst = isnull(@lst+',','')+AF.AwardFocusName
    from AwardFocus as AF
    inner join AwardFoccusNomination as AFN
        on AF.AwardFocusID = AFN.AwardFocusID
    where AFN.NominationID=@Id


    return @lst

END

Magnus · Answer 3 · 2011-11-28T21:33:50.730

I think the best solution would be to create a User defined aggregate that concatenates the values (in a group) into a comma separated list. See Example 1 at: http://msdn.microsoft.com/en-us/library/ms131056.aspx

Usage:

SELECT 
     Nomination.NominationId, 
     Nomination.Created,
     Nomination.Updated,
     dbo.Concatenate(AwardFocus.AwardFocusName) As Names
FROM 
     Nomination
     JOIN NominationAwardFocus 
       ON Nomination.NominationId = NominationAwardFocus.NominationId 
     JOIN AwardFocus
       ON NominationAwardFocus.AwardFocusId = AwardFocus.AwardFocusId
GROUP BY  
     Nomination.NominationId, 
     Nomination.Created,
     Nomination.Updated

sql server sub query with a comma separated resultset

3 Answers3

Linked