I know it is legacy database, but i have been looking how can i get

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)

I managed to get the data in the format desired with @table vars +
while + update:

-- This code works, but is kinda nasty
declare @tabla table(numcener tinyint, numpedid int, numlnent
nvarchar(5))
declare @retorno table(numcener tinyint, numpedid int, entregas
nvarchar(4000))
insert into
@tabla(numcener, numpedid, numlnent)
(select numcener, numpedid, convert(nvarchar(5),numlnent) from
tcolinen where numcener = 99 and numpedid < 2520)
select * from @tabla
insert into @retorno(numcener, numpedid, entregas)
(select numcener, numpedid, min(numlnent) from @tabla group by
numcener, numpedid)
delete t from @tabla t inner join
(select numcener, numpedid, min(numlnent) minimo from @tabla group by
numcener, numpedid) v on t.numcener = v.numcener and t.numpedid =
v.numpedid and t.numlnent = v.minimo
while exists (select numcener from @tabla)
begin
update r set r.entregas = r.entregas + ',' + v.agregar from @retorno
r inner join
(select t.numcener, t.numpedid, min(t.numlnent) agregar from
@retorno r left join @tabla t on t.numcener = r.numcener and
t.numpedid = r.numpedid where not t.numcener is null group by
t.numcener, t.numpedid) v on r.numcener = v.numcener and r.numpedid =
v.numpedid
delete t from @tabla t inner join
(select numcener, numpedid, min(numlnent) minimo from @tabla group
by numcener, numpedid) v on t.numcener = v.numcener and t.numpedid =
v.numpedid and t.numlnent = v.minimo
end
select * from @retorno

Is there a less dirty way to do such that thing?

bradbury9 (ray.bradbury9 (AT) gmail (DOT) com) writes:

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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On 1 feb, 12:36, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
Well, the code does work. My guess is that was no nice and clean
solution but was not sure about it.

Thanks or the feedback.