I had a need to find a way to take a single column result set and put it into a single varchar variable. I did not want to go through the hassle or the overhead of doing a cursor for this. With a little Googling, I found the below article:
http://stackoverflow.com/questions/822615/what-is-the-best-way-to-collapse-the-rows-of-a-select-into-a-string
It shows a very nice way of solving my problem :
DECLARE @A VARCHAR(max)
SET @A = ''
SELECT @A = @A + ISNULL(mt.ColA, '') + ', '
FROM dbo.MyTable AS mt
ORDER BY mt.ColA
SET @A = SUBSTRING(@A, 1, LEN(@A) - 2)
SELECT @A
I thought this was a pretty nice way to do what I was doing but then I thought of how we are constantly having to do cursors to generate files and realized that we could use this method to generate a file without using a cursor. The below code will add a line at a time to the variable and then the variable can be written to file.
DECLARE @A VARCHAR(max)
SET @A = ''
SELECT @A = @A + ISNULL(mt.ColA, '') + ',' + ISNULL(mt.ColB, '') + CHAR(10)
FROM dbo.MyTable AS mt
ORDER BY mt.ColA
SELECT @A
--Write @A to a file
Using the above method reduces considerably the code required to generate a file and makes it a lot easier to read. Look over it and let me know if you have any comments or recommendations.
Update: Found an issue with the above method. We were using this at work with a process that would work sometimes and not in at other times. After a bunch of playing and searching, I found the below queries:
- http://blog.sqlauthority.com/2009/09/29/sql-server-interesting-observation-execution-plan-and-results-of-aggregate-concatenation-queries/
- http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/
The first link essentially says that using the order by statement may cause unexpected results inconsistently. Meaning that it will work one time but may not the next.
The second link gives the alternative by using a FOR XML PATH query hint. Very helpful
