Denormalizing A Column Into A String

If you wanted to take query results and turn them into a string, how would you go about it? Traditionally, you may use a dreaded cursor/while loop, or a best, a complex, poorly performing UDF. But since the introduction SQL 2005 and native XML, there is a nice little trick you can use.

As an example, I am going to use a scenario from a question I answered on MSDN. Basically, the requirement was to take a list of values, turn them into a string, and then insert the average value into the middle. So the first thing I am going to do, is use a Table Variable to generate the list...

DECLARE @values table(col1 float)

INSERT INTO @values VALUES(2), (2), (3), (4), (5)

...Next, I am going to find the average value, and insert this into the list. For simplicity, I will use a second table variable...

DECLARE @values2 table(col1 float)

INSERT INTO @values2
SELECT AVG(col1) AS col1 FROM @values
UNION ALL
SELECT col1 FROM @values
ORDER BY col1

...So, now I have the list of values I need, pre-sorted in a table variable, It's time for the interesting bit. Basically, I am going to use the data() X-Query function, which returns a typed value for each item specified, inside a sub-query, with a FOR XML clause. The outer query will have no FOR XML clause, so the results will be returned as relational data...

SELECT DISTINCT ConCat_Column =
(
SELECT CAST(col1 as decimal(3,2))  AS [data()]
FROM @values2
FOR XML PATH ('')
)

...The results are as follows. I have highlighted the average value in bold...

2.00 2.00 3.00 3.20 4.00 5.00

...Very cool eh?