sql server programming: I have thousands of rows where data is like the following:


NAME ANIMAL SOUND DESC ACT EST
----- ------ ----- --------- --- ----
spot dogs 1d jump 0 0
spot dogs 1d bark 5 10
beauty horses 2c whine 10 10
beauty horses 2c 5 15
suki cats 1a meow 5 15
rover fox 44f howl 10 15
rover fox 52e bark 1 10
rover fox 52e cry 0 0


The user wants the data grouped by NAME and ANIMAL where SOUND and DESC
is concatenated for each instance of NAME and ANIMAL so the result
would be like that below. The ACT and EST are summed for each group.
All columns are varchar with the exception of the last two columns.
There can be nulls in any of the columns


NAME ANIMAL SOUND DESC ACT EST
----- ------ ----- --------- --- ----
spot dogs 1d jump bark 5 10
beauty horses 2c whine 20 25
sku cats 1a meow 5 15
rover fox 44f 52e howl bark cry 11 25


How would the code for this look to produce the results above?


Thanks in advance for your help.

There is no built-in operators for sequential row concatenators in t-SQL.
Generally, if the number of values that are to be concatenated are a few,
you can write a series of CASE expressions inside an aggregate function with
a GROUP BY clause. For instance see:
http://groups.google.ca/group/microsoft.public.sqlserver.programming/msg/794aff53bfa47c1b
If the number of rows in the group are not knowm upfront, you will have to
look for some other alternatives:
http://groups.google.ca/group/microsoft.public.sqlserver.programming/msg/2d85bf366dd9e73e

But in any case, the recommended approach is to retrieve the resultset to
the client side application or report writer & massage the required
information in the desired format.

--
Anith

Ann,

A cursor might be as fast as anything here. The solution below uses
a cursor for the aggregation, and also passes the string list through a
sorting function to alphabetize the values and eliminate duplicates. It's
only the sorting routine that needs the fixed-width fields for the SOUND
and DESC, so if you don't care about sorting and duplicates, you can
avoid all the padding and elminate much of the code.

Steve Kass
Drew University




create table T (
[NAME] varchar(20),
ANIMAL varchar(20),
SOUND varchar(10),
[DESC] varchar(10),
ACT int,
EST int
)

insert into T values('spot','dogs','1d','jump',0,0)
insert into T values('spot','dogs','1d','bark',5,10)
insert into T values('beauty','horses','2c','whine',10,10)
insert into T values('beauty','horses','2c','',5,15)
insert into T values('suki','cats','1a','meow',5,15)
insert into T values('rover','fox','44f','howl',10,15)
insert into T values('rover','fox','52e','bark',1,10)
insert into T values('rover','fox','52e','cry',0,0)
go

create function sk_merge(
@s_l varchar(1005),
@s_r varchar(1005)
) returns varchar(2000) as begin

declare @l int, @r int
set @l = datalength(@s_l) set @r = datalength(@s_r)
declare @ln varchar(10), @rn varchar(10)
declare @s_out varchar(2000)
set @s_out = ''
while @l*@r > 0 begin
set @ln = substring(@s_l,@l-9,10)
set @rn = substring(@s_r,@r-9,10)
set @s_out =
case
when @ln > @rn
then @ln
when @ln < @rn
then @rn
else '' end + @s_out
set @l = @l -
case
when @ln > @rn
then 10
else 0 end
set @r = @r -
case
when @ln > @rn
then 0
else 10 end
end
if @l > 0 set @s_out = left(@s_l,@l)+@s_out
if @r > 0 set @s_out = left(@s_r,@r)+@s_out
return @s_out
end
go

create function sk_mergesort(
@s_in varchar(2000)
) returns varchar(2000) as begin

declare @s_out varchar(2000)
return ''
end
go

alter function sk_mergesort(
@s_in varchar(2000)
) returns varchar(2000) as begin
declare @s_out varchar(2000)
if datalength(@s_in) <= 10
set @s_out = @s_in
else begin
declare @s_in_l varchar(1005)
declare @s_in_r varchar(1005)
declare @s_out_l varchar(1005)
declare @s_out_r varchar(1005)
declare @mid int
set @mid = datalength(@s_in)/10/2*10
set @s_in_l = substring(@s_in,1,@mid)
set @s_in_r = substring(@s_in,@mid+1,1005)
set @s_out_l = dbo.sk_mergesort(@s_in_l)
set @s_out_r = dbo.sk_mergesort(@s_in_r)
set @s_out = dbo.sk_merge(@s_out_l,@s_out_r)
end
return @s_out
end
go

declare @Result table (
[NAME] varchar(20),
ANIMAL varchar(20),
SOUND varchar(2000),
[DESC] varchar(2000),
ACT int,
EST int
)

DECLARE C CURSOR FAST_FORWARD FOR
SELECT [NAME], ANIMAL, SOUND, [DESC], ACT, EST
FROM T
ORDER BY [NAME]

OPEN C

BEGIN TRAN
DECLARE @currN varchar(20), @N varchar(20),
@nextS char(10), @S varchar(2000),
@nextD char(10), @D varchar(2000),
@A varchar(20), @ACT int, @EST int,
@ACTsum int, @ESTsum int,
@currA varchar(20)
FETCH NEXT FROM C
INTO @N, @A, @nextS, @nextD, @ACT, @EST
WHILE @@fetch_status = 0 BEGIN
SET @S = @nextS
SET @D = @nextD
SET @ACTsum = @ACT
SET @ESTsum = @EST
SET @currN = @N
SET @currA = @A
FETCH NEXT FROM C INTO @N, @A, @nextS, @nextD, @ACT, @EST
WHILE @@fetch_status = 0 AND @N = @currN BEGIN
SET @S = @S + @nextS
SET @D = @D + @nextD
SET @ACTsum = @ACTsum + @ACT
SET @ESTsum = @ESTsum + @EST
FETCH NEXT FROM C INTO @N, @A, @nextS, @nextD, @ACT, @EST
END
INSERT INTO @Result
SELECT @currN, @currA,
dbo.sk_mergesort(@S),
@D, @ACTsum, @ESTsum
END
COMMIT TRAN

CLOSE C
DEALLOCATE C

SELECT * FROM @Result
GO

DROP TABLE T
DROP FUNCTION sk_merge, sk_mergesort



[quoted text, click to view]