Chris Morris-439714 (10/29/2010)
I'd really like to see an example of that
I'll post one in a minute.
...in the meantime, here's another simple and obvious use of rCTE's. No timings because I've not yet had the opportunity.
Table Spool mania! It doesn't perform well; I'd stick with the usual XML hack/solution:
SELECT Sections.Section,
Concatenated.sentence
FROM (
SELECT DISTINCT
Section
FROM #Test
) Sections
CROSS
APPLY (
SELECT SPACE(1) + Word
FROM #Test T
WHERE T.Section = Sections.Section
ORDER BY
T.TestID
FOR XML PATH (''),
TYPE
) Concatenator (xml_string)
CROSS
APPLY (
SELECT STUFF(Concatenator.xml_string.value('(./text())[1]', 'VARCHAR(MAX)'), 1, 1, SPACE(0))
) Concatenated (sentence);