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