Thursday 16 April 2015

sql server sub query with a comma separated resultset

I have above table structure and if i want to show my child table's value comma separated then below is my solution.

 SELECT n.nominationID
        , SUBSTRING((
                            SELECT ',' + af.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

And another solutions is
DECLARE @listStr VARCHAR(MAX)
(SELECT @listStr = COALESCE(@listStr+',' , '') + c.Code FROM RefferalCPTCodes  r inner join CPTCode c on r.CPTCodeID=c.id where ReferralID=80) 
SELECT @listStr

No comments:

Post a Comment