Steve Stedman has an interesting use for common table expressions:
Years ago while working on my CTE presentation for a SQL Saturday I added a blog post called “Using a CTE to Split a String Into Rows“, and since that posting I have used it many times. But as things go in development, eventually there is a need to do something more.
Doing some complex string building to create files lately I cam across the need to use a CTE to split strings into rows but to also include line numbers. To accomplish that I first started with the same function that I used last year, but renamed it, and added a second output column called LineNumber, here is what it looked like:
Click through for the script, as well as some additional notes.