Convert a SQL Join Query statement into comma separated rows

Question

I need to convert rows from a SQL Server database into comma-separated values but before I do that I need to look up an id value from a lookup table. The main table holds a index value and the lookup table has the text so my Select statement looks like,

 SELECT 
     dbo.tbProgramGrant.ProgramPercent, 
     dbo.tbProgramGrant.GrantFundingID, 
     dbo.tbProgram.Acronym
 FROM 
     dbo.tbProgram 
 INNER JOIN
     dbo.tbProgramGrant ON dbo.tbProgram.ProgramID = dbo.tbProgramGrant.ProgramID

If I save it as a view with the name vwProgramCode I can create my comma separated value using:

SELECT DISTINCT 
    p1.GrantFundingID, 
    STUFF ((SELECT ', ' + RTRIM(p.Acronym) + ':' + CONVERT(nvarchar(15), p.ProgramPercent)
            FROM vwProgramCode p  
            WHERE p.GrantFundingID = p1.GrantFundingID  
            FOR XML PATH('')), 1, 1, '') AS codelist
FROM
    vwProgramCode p1
GROUP BY
    p1.GrantFundingID

However I would prefer not to use the view but I can't get the syntax correct injecting the view Select statement in the Select stuff statement.

I was wondering if someone could point me in the right direction.

Thanks

Which version of SQL Server are you using `select @@version` — Charlieface, Aug 16 '21 at 02:29
There is also another thread similar here: https://stackoverflow.com/questions/21760969/multiple-rows-to-one-comma-separated-value-in-sql-server — Rishi, Aug 16 '21 at 03:17
@Rishi not really... OP is actually asking how to embed a view into their query, not how to concat a string. — Dale K, Aug 16 '21 at 03:41
select @@version -- both 2008- but being sun retired - but then 2016 and 2019 — Bill, Aug 16 '21 at 10:32

score 4 · Accepted Answer · edited Aug 16 '21 at 02:31

Use a Common Table Expression.

WITH cte AS (
    SELECT dbo.tbProgramGrant.ProgramPercent, dbo.tbProgramGrant.GrantFundingID, dbo.tbProgram.Acronym
    FROM dbo.tbProgram
    INNER JOIN dbo.tbProgramGrant ON dbo.tbProgram.ProgramID = dbo.tbProgramGrant.ProgramID
)
SELECT p1.GrantFundingID
    , STUFF((
        SELECT ', ' + RTRIM(p.Acronym) +':' + CONVERT(nvarchar(15), p.ProgramPercent)
        FROM cte  p  
        WHERE p.GrantFundingID = p1.GrantFundingID  
        FOR XML PATH(''), TYPE
    ).value('text()[1]','nvarchar(max)'), 1, 2, '') AS codelist
FROM cte p1
GROUP BY p1.GrantFundingID;

Convert a SQL Join Query statement into comma separated rows

1 Answers1