Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #918

Custom agregation in Sql server 6

Show key headers only | View raw

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?

Back to comp.databases.ms-sqlserver | Previous | Next — Next in thread | Find similar

Thread

Custom agregation in Sql  server 6 bradbury9 <ray.bradbury9@gmail.com> - 2012-02-01 03:21 -0800
  Re: Custom agregation in Sql server 6 Erland Sommarskog <esquel@sommarskog.se> - 2012-02-01 11:36 +0000
    Re: Custom agregation in Sql server 6 bradbury9 <ray.bradbury9@gmail.com> - 2012-02-01 06:27 -0800

csiph-web