带有逗号分隔结果集的 sql server 子查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8302337/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
sql server sub query with a comma separated resultset
提问by obautista
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 张桌子的图片。我需要执行一个选择，返回第一个表中的记录，并包含屏幕截图中第三个表中存在的最后一个 AwardFocusName。
So my result set would return one record and include the list of AwardFocusNames in it (comma separated).
所以我的结果集将返回一个记录，并在其中包含 AwardFocusNames 列表（逗号分隔）。
回答by Alex
Here's a trick I've used in the past to do similar things. Use SUBSTRINGfunction.
这是我过去用来做类似事情的一个技巧。使用SUBSTRING函数。
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".
请注意，2 用于切断子选择添加到第一项的前导逗号，并且选择 1000000 作为一个大数字以表示“字符串的所有其余部分”。
回答by Johnie Karr
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/
回答by wcm
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
回答by Magnus
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 1at: 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
