Concatenating Text using FOR XML

In my recent post about using CTE’s to concatenate text (see http://marcjellinek.wordpress.com/2011/07/10/cte-to-concatenate-column-values-in-multiple-rows-into-a-single-string/), I was very proud of myself and my genius.

Then I read Majunath Bhat’s post “Grouping data from multiple rows into a single row” over on BeyondRelational (see http://beyondrelational.com/blogs/manjunathcbhat/archive/2011/08/07/grouping-data-from-multiple-rows-into-single-row.aspx)

With some small changes, I now see the beauty in using FOR XML PATH to do the string aggregation for me.

For example, to see a list of databases on a server:

SELECT
     CASE
          WHEN LEN(name_string) > 0
          THEN LEFT(name_string, LEN(name_string) - 1) -- trim off the final comma
     END as name_string 
FROM
     ( 
          SELECT
               REPLACE(REPLACE(names, '<name>', ''), '</name>', ', ') as name_string  -- strip out <name></name>
          FROM
               ( -- get list of names from sys.databases.  Each name will be surrounded by <name></name>
                    SELECT
                         name
                    FROM
                         sys.databases FOR XML PATH('')
               ) data (names) -- define the column name as 'names'
     ) final

A few cool things I learned:

FOR XML will aggregate data into a string for you. No need to mess with PIVOT or Common Table Expressions

Table aliases can also contain column aliases.

How about when you want aggregations based on some grouping?  This will give you a comma-delimited list of columns for each table within your database:

SELECT
     table_name,
     CASE
          WHEN LEN(column_list) > 0
          THEN LEFT(column_list, LEN(column_list) - 1)
     END as column_list
FROM
     (
          SELECT
               table_name,
               REPLACE(REPLACE(column_list, '<name>', ''), '</name>', ', ') column_list
          FROM
               (
                    SELECT
                         t.name table_name,
                         (SELECT name FROM sys.columns c WHERE t.object_id = c.object_id FOR XML PATH('')) column_list
                    FROM
                         sys.tables t
               ) data
     ) final_data

Cool, huh?

About these ads

, ,

  1. #1 by JaimePR on March 28, 2012 - 1:45 pm

    Hello,
    I’ve been reading more about treating strings using FORXML combined with STUFF function.
    For the database example you could do:

    SELECT STUFF(
    (SELECT TOP(5) ‘,’ + name FROM sys.databases FOR XML PATH(”)),
    1,
    1,
    ”) AS dbs

    It’s less code and more simple.

    • #2 by Marc Jellinek on March 28, 2012 - 3:23 pm

      You are right. My example can be reduced to:

      SELECT
      t.name,
      STUFF(
      (SELECT ‘,’ + c.name
      FROM sys.columns c
      WHERE t.object_id = c.object_id
      FOR XML PATH(”)), 1, 1, ”) as cols
      FROM
      sys.tables t

  1. CTE to concatenate column values in multiple rows into a single string « rnd(stuff)

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

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: