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