Path: csiph.com!x330-a1.tempe.blueboxinc.net!newsfeed.hal-mli.net!feeder3.hal-mli.net!newsfeed.hal-mli.net!feeder1.hal-mli.net!eternal-september.org!feeder.eternal-september.org!mx04.eternal-september.org!.POSTED!not-for-mail From: Erland Sommarskog Newsgroups: comp.databases.ms-sqlserver Subject: Re: Custom agregation in Sql server 6 Date: Wed, 1 Feb 2012 11:36:21 +0000 (UTC) Organization: Erland Sommarskog Lines: 34 Message-ID: References: <31c22e19-fb3d-4707-8efd-4027736db61d@eb6g2000vbb.googlegroups.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Injection-Date: Wed, 1 Feb 2012 11:36:21 +0000 (UTC) Injection-Info: mx04.eternal-september.org; posting-host="G7+Jz22XqYCG8C6rb1H3YA"; logging-data="21212"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX182Mglier3LVvs2Y3oOxtOD" User-Agent: Xnews/2005.10.03 Mime-proxy/1.4.c.4 (Win32) Cancel-Lock: sha1:5KSggBhHpDt+4AB7XQ2a7rLjGcg= Xref: x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:919 bradbury9 (ray.bradbury9@gmail.com) writes: > Input data > > Col1, col2 > 1, 'nebraska' > 1, 'boston' > 2, 'new york' > 3, 'pekin' > > results desired: > Col1 col2_concatenated > 1 'nebraska,boston' > 2 'new york' > 3 'pekin' > > The problem i find is using sql server 6 (sql server 2000 with > backward 6.0 compatibility actually). > I cant do custom agregate functions (those are sql 2005 or later) > I cant do "FOR XML PATH" (2005 or later also) In SQL 2000, no matter the compatibility mode, creating comma-separated lists means lots of dirty work. I didn't check your solution in detail, but if you have it work, there is no idea to look for anything better. Well, it is better to do it the client, that's where presentation belongs. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx