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).

Answer 1

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".

Answer 2

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