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. #1 by Chris Dorch on July 11, 2013 - 2:59 pm

    This operation is good, but it has quite a bit of overhead using For XML.. COALESCE would be better… Here’s an example…

    CREATE TABLE test (col1 VARCHAR(50))
    INSERT test
    SELECT ‘Bumblebee’
    UNION
    SELECT ‘Optimus Prime’
    UNION
    SELECT ‘Ratchet’

    DECLARE @Concat VARCHAR(1000)

    SELECT @Concat = COALESCE(@Concat,”) + Col1 + ‘;’
    FROM test

    SELECT @Concat AS Transformers

    DROP TABLE test

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: