sql server子查询用逗号分隔的结果集

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.

我附上了3张桌子的图片。我需要做一个select，它返回第一个表中的记录，并包含屏幕截图中第3个表中存在的最后一个AwardFocusName。

So my result set would return one record and include the list of AwardFocusNames in it (comma separated).

所以我的结果集将返回一条记录，并在其中包含AwardFocusNames列表（以逗号分隔）。

4 个解决方案

#1

Here's a trick I've used in the past to do similar things

这是我过去用来做类似事情的技巧

    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

#2

I think the best solution would be to create a User defined aggregate that concatenates the values (in a group) into a comma separated list. See Example 1 at: http://msdn.microsoft.com/en-us/library/ms131056.aspx

我认为最好的解决方案是创建一个用户定义的聚合，将值（在一个组中）连接成一个以逗号分隔的列表。请参阅示例1：http：//msdn.microsoft.com/en-us/library/ms131056.aspx

Usage:

用法：

SELECT 
     Nomination.NominationId, 
     Nomination.Created,
     Nomination.Updated,
     dbo.Concatenate(AwardFocus.AwardFocusName) As Names
FROM 
     Nomination
     JOIN NominationAwardFocus 
       ON Nomination.NominationId = NominationAwardFocus.NominationId 
     JOIN AwardFocus
       ON NominationAwardFocus.AwardFocusId = AwardFocus.AwardFocusId
GROUP BY  
     Nomination.NominationId, 
     Nomination.Created,
     Nomination.Updated

#3

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

#4

I explain how to do this on my blog, here:

我在这里解释了如何在我的博客上执行此操作：

http://johniekarr.wordpress.com/2011/08/08/pushing-multiple-results-into-one-column/

http://johniekarr.wordpress.com/2011/08/08/pushing-multiple-results-into-one-column/