concatenate rows into a column

Question

concatenate rows into a column

Jane Matheson-154829

SSCertifiable

Points: 5522
More actions
January 20, 2005 at 3:05 pm

#60224

Does anyone know how to concatenate rows into a column for SOME of the output of a select statement?
I'll use an example from sysdatabases to illustrate what I'm trying to do:
This syntax works but it returns the COMPTlevel concatenated together.
declare @name_list varchar(1000)
declare @compatiblity varchar(1000)
set @name_list = ''
set @compatiblity = ''
select @compatiblity = @compatiblity + cast(cmptlevel as varchar(5)),
@name_list = @name_list + name +',' from master..sysdatabases
select @compatiblity,left(@name_list, len(@name_list) - 1) as databases
What I'd ideally like is a result set GROUPED by comptlevel and then all the database names for that level concatenated together in one field of the output. This is logically what I want but it doesn't work. The result is not a set - and the group by doesn't pass the parser.
declare @name_list varchar(1000)
declare @compatiblity varchar(1000)
-- this put it in one field
set @name_list = ''
set @compatiblity = ''
select @compatiblity = cast(cmptlevel as varchar(5)),
@name_list = @name_list + name +',' from master..sysdatabases
group by cmptlevel
select @compatiblity,left(@name_list, len(@name_list) - 1) as databases
Any new tricks? I've tried a coorelated subquery for the database list part but I can't get it past the parser.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

The fact, that this aggregate concatenation queries work, is amazingly enough when you consider this KB article http://support.microsoft.com/default.aspx?scid=kb;EN-US;287515

SQL Server MVP Adam Machanic has a nice demonstration, that might give you some ideas. Here's his homepage http://www.sqljunkies.com/weblog/amachanic/ It seems to be down right now, so I can't post the full link.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Jane Matheson-154829 SSCertifiable Points: 5522 More actions · Answer 2

Thanks for the links. I had not found the MS one yet. The other site appears to still be not functioning quite right.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Well, seems to be still down

Adam is a regular visitor here. I dropped him a PM, but I would keep trying to reach his blog.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Adam Machanic SSCoach Points: 15370 More actions · Answer 4

The site is really in bad shape today, but this direct link into the article appears to work:

http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx

--
Adam Machanic
whoisactive