Archive for July, 2013

Concatenating multiple rows into a single string in SQL Server

I wanted to return the results of a SELECT statement into a single string, because those were the requirements of my huge, mufti-national company that deals with cartoon character databases. So through the magic of “FOR XML”, I give you this:

SELECT c.Name
,(SELECT CAST(cs.CharacterName + ‘;’ AS [text()]
FROM Cartoon c
INNER JOIN Characters cs
ON c.CartoonID = cs.CartoonID
AND c.Name = ‘Transformers’
AND cs.Group = ‘Autobots’
FOR XML PATH (”)) AS Autobots
FROM Cartoon c

This returns:
Transformers    Bumblebee;Optimus Prime;Ratchet;

So we have the all-important list of Autobots in a wonderful semi-colon-separated string. The uses of Autobot lists are, of course, limitless.

Advertisements

1 Comment