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
– Charlieface, Commented Aug 16, 2021 at 2:29
There is also another thread similar here: stackoverflow.com/questions/21760969/… — Rishi
– Rishi, Commented Aug 16, 2021 at 3:17
@Rishi not really... OP is actually asking how to embed a view into their query, not how to concat a string. — Dale K
– Dale K, Commented Aug 16, 2021 at 3:41
select @@version -- both 2008- but being sun retired - but then 2016 and 2019 — Bill
– Bill, Commented Aug 16, 2021 at 10:32

Charlieface · Accepted Answer · 2021-08-16 02:31:29Z

5

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;

edited Aug 16, 2021 at 2:31

Charlieface

78.4k8 gold badges35 silver badges77 bronze badges

answered Aug 16, 2021 at 1:58

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Convert a SQL Join Query statement into comma separated rows

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related