SQLTeam.com | Weblogs | Forums

Concatenate row-Values into string


#1

Hi,
‌my Cross-Apply returns multiple Values. How can i bring these Values to 1 String (V1,V2,V3....) into my MainSelect?


‌SELECT distinct
a.[NAME]
,c.OBJEKTID -- multiple Values-String
FROM [Devices] a
cross apply
(
Select objektid from [Devices] b
where a.OBJEKTID = b.OBJEKTID
) c

Name OBJEKTID
d100 1001
d100 1002
d100 1003
d100 1004
d101 1011
d101 1012

Result
d100 1001,1002,1003,1004
d101 1011,1012

Regards
Nicole
:ermm:‌‌‌‌


#2

Here's my template for doing this job:

SELECT	H.HeaderCol1
	, H.HeaderCol2
	, [MyValueList] = STUFF(
		(
			SELECT	',' + C.StringColToList
-- ** Use COALESCE(C.StringColToList, '') to preserve values that can be NULL
-- ** OR ** for NUMERIC values use:
			SELECT	',' + CONVERT(varchar(20), C.NumericColToList)
			FROM	dbo.ChildTable AS C
			WHERE	C.MyKey = H.MyKey
			ORDER BY C.C_ID
-- ** And / Or use the column itself for the delimited list to be "in order":	, C.ColToList
			FOR XML PATH(''), TYPE
		).value('.', 'varchar(max)')	-- NOTE: "'value" is case sensitive!!
	        , 1, 1, '')
-- For a 2 character delimiter e.g. ", " use:	, 1, 2, '')
FROM	dbo.HeaderTable AS H

#3

Didn't the answer you got on SQLServerCentral work for you?