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