带有逗号分隔的结果集的sql server子查询 [英] sql server sub query with a comma separated resultset

问题描述

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

I need to return records on a table and my result set needs to contain a comma separated list.

我已附上3张桌子的图像.我需要做一个选择，以返回第一个表中的记录，并在屏幕快照的第三个表中包含AwardFocusName的最后一个.

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.

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

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

推荐答案

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

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

请注意，使用2来剪掉子选择项添加到第一项的前导逗号，并选择1000000作为较大的数字来表示字符串的所有其余部分".

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

这篇关于带有逗号分隔的结果集的sql server子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！