Simple syntax error in the value function
😎This should work
SELECT
d.[currType]
,STUFF(
(SELECT
'; ' + d.currName
from [foo].[dbo].[zool] d
where f.[issuenum] = d.[issuenum]
and f.[slipnum] = d.[slipnum]
and f.[comicsName] = d.[comicsName]
FOR XML PATH(''), TYPE).value('(./text())[1]','NVARCHAR(2000)'),1,1,'') AS [currNames]
FROM [foo].[dbo].tbl] f
LEFT outer join [foo].[dbo].[Zool] d
on f.[billNo] = d.[billNo];
Thank you, Is there a correlated query or self join approach that could be used to keep the data type of the colmn from becoming a blob?
No need for that, the value function does the conversion to nvarchar(2000) in this case.
😎
The text() function also avoids the construct of an XML for the output, cuts the effort almost in half. The role of the stuff function is simply to remove the leading semicolon, does not contribute to the concatenation.