sql server sub query with a comma separated resultset
Solution 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".
Solution 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
Solution 3
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
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
Related videos on Youtube
obautista
Updated on December 11, 2021Comments
-
obautista over 2 years
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).
-
obautista over 12 yearsTable names are Nomination, AwardFocus, NominationAwardFocus (in the order they appear in the screenshot).
-
Gert Arnold over 2 yearsDuplicate: Comma separated results in SQL
-
-
Mikael Eriksson over 12 yearsYou should try this with an
awardFocusName
containing an&
. It's not pretty. -
Hagelt18 over 10 yearsI did run into the & issue, but a simple REPLACE solved the problem.
-
sohaiby about 9 yearsI wish I can vote multiple times for this answer.. You save my day sir, Thank you :)
-
Naner over 7 yearsThanks! I wish there was a function like the GROUP_CONCAT in MySQL
-
Matthew over 5 yearsSee here for a similar solution using the STUFF function stackoverflow.com/a/31212160/8595398