Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Custom agregation in Sql server 6
4 views
Skip to first unread message
bradbury9
Feb 1, 2012, 6:21:44 AM2/1/12
to
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?
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?
Erland Sommarskog
Feb 1, 2012, 6:36:21 AM2/1/12
to
bradbury9 (ray.br...@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
> 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)
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, esq...@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
bradbury9
Feb 1, 2012, 9:27:50 AM2/1/12
to
On 1 feb, 12:36, Erland Sommarskog <esq...@sommarskog.se> wrote:
solution but was not sure about it.
Thanks or the feedback.
> bradbury9 (ray.bradbu...@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, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> > 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, esq...@sommarskog.se
>
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
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.
0 new messages