SQL: Concatenate rows to a string list

Sometimes business wants a column in several rows concatenated to one string list.
I found this gem:
http://stackoverflow.com/a/11891963/7266988

Still working on it, but here’s my cleaner code for better readabity:

;with basetable as (
	SELECT		id,
				CAST(name as varchar(max)) name, 
				ROW_NUMBER() OVER(Partition By id order by countRecords) rowNumber, 
				COUNT(*) OVER (Partition By id) countRecords 
	FROM		(
					VALUES	(1, 'Johnny', 1),
							(1,'M', 2), 
							(2,'Bill', 1),
							(2, 'S.', 4),
							(2, 'Preston', 5),
							(2, 'Esq.', 6),
							(3, 'Ted', 1),
							(3,'Theodore', 2),
							(3,'Logan', 3),
							(4, 'Peter', 1),
							(4,'Paul', 2),
							(4,'Mary', 3)
				) g(id, name, countRecords)
), rCTE as (
    SELECT		basetable.countRecords,
				basetable.id,
				basetable.name,
				basetable.rowNumber
	from		basetable
	where		rowNumber=1
    UNION ALL
    SELECT		basetable.countRecords,
				rCTE.ID,
				rCTE.name +', '+ basetable.name name,
				rCTE.rowNumber+1
    FROM		basetable
				inner join
				rCTE
				on	basetable.id = rCTE.id
					and
					basetable.rowNumber = rCTE.rowNumber + 1
)

SELECT		name
FROM		rCTE
WHERE		countRecords = rowNumber
			and
			ID=4
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: