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

我需要返回一个表上的记录，我的结果集需要包含一个逗号分隔的列表.

我附上了一张 3 张桌子的图片.我需要执行一个选择，返回第一个表中的记录，并包含屏幕截图中第三个表中存在的最后一个 AwardFocusName.

所以我的结果集将返回一条记录并在其中包含 AwardFocusNames 列表(逗号分隔).

解决方案

这是我过去用来做类似事情的一个技巧.使用 SUBSTRING 函数.

<前>SELECT n.nominationID, 子串((SELECT ',' + naf.awardFocusName来自提名奖Focus naf加入奖Focus afON naf.awardFocusID = af.awardFocusID其中 n.nominationID = naf.nominationIDFOR XML 路径('')), 2, 1000000)来自提名 n

请注意，2 用于切断子选择添加到第一项的前导逗号，并且选择 1000000 作为一个大数字以表示字符串的所有其余部分".

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

解决方案

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