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