Paul White NZ (2/27/2010)
...recursive CTEs can be fast, but it is relative. I sometimes use them to seek down the distinct keys of a large index rather than scanning the whole thing and relying on an aggregate, for example...Paul
I'd really like to see an example of that - in the meantime, here's another simple and obvious use of rCTE's. No timings because I've not yet had the opportunity.
-- Rollup rows, concatenating row values into a new column
DROP TABLE #Test
CREATE TABLE #Test (TestID INT IDENTITY (1,1), Section INT, Word VARCHAR(20))
INSERT INTO #Test (Section, Word) VALUES
(1, 'A'),(1, 'day'), (1, 'in'),(1, 'the'),(1, 'life'),(1, 'of'),(1, 'Ivan'),(1, 'Denisovich'),
(2, 'Silent'),(2, 'Spring')
;WITH PreparedData AS (
SELECT *,
SectionID = ROW_NUMBER() OVER(PARTITION BY Section ORDER BY TestID), -- sets order of words in sentence
SectionSize = COUNT(*) OVER(PARTITION BY Section) -- used to detect last word of sentence
FROM #Test),
Concatenator AS (
SELECT TestID, Section, Word, -- source columns
SectionID, SectionSize, Sentence = CAST(Word AS VARCHAR(100)) -- "working" columns
FROM PreparedData
WHERE TestID = 1
UNION ALL
SELECT
t.TestID, t.Section, t.Word,
t.SectionID, t.SectionSize, Sentence = CAST(CASE WHEN t.SectionID = 1 THEN ISNULL(t.Word, '')
ELSE l.Sentence + ' ' + ISNULL(t.Word, '') END AS VARCHAR(100))
FROM PreparedData t
INNER JOIN Concatenator l ON l.TestID + 1 = t.TestID
)
SELECT
TestID,
Section,
Sentence
FROM Concatenator
WHERE SectionID = SectionSize
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden