String Aggregate Function

Question

Post reply

String Aggregate Function

Rohit @ SQL

SSC Eights!

Points: 938
More actions
December 29, 2003 at 3:46 am

#83274

Hi to all,
I need to know is there any way to aggregate the strings in a group
for example
i have table temp
Temp
-------------
Id Name
-------------
1 Abc
1 xyz
1 PQr
2 Mlo
2 stp
3 a12
Now i need the results like
1 Abc,XYZ,Pqr
2 Mlo,stp
3 a12

Rohit

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

create table #tmp1 (SeqNo int not null identity(1,1) ,col1 int, col2 varchar(125))

go

set nocount on

insert into #tmp1 (col1,col2) values(1, 'Abc')

go

insert into #tmp1 (col1,col2) values(1, 'xyz')

go

insert into #tmp1 (col1,col2) values(1, 'PQr')

go

insert into #tmp1 (col1,col2) values(2, 'Mlo')

go

insert into #tmp1 (col1,col2) values(2, 'stp')

go

insert into #tmp1 (col1,col2) values(3, 'a12')

go

create table #tmp2 (Orig_SeqNo int not null default 0 , col1 int, col2 varchar(125) not null default '')

go

Insert into #tmp2 (col1)

select col1 from #tmp1 group by col1

go

declare @ctr int

set @ctr = 1

Declare @CommaChar varchar(1)

set @CommaChar = ''

--set nocount off

WHILE @ctr > 0/*set into loop until no more data can be found*/

BEGIN

update C2

set Orig_SeqNo = c1.SeqNo

, col2 = C2.col2 + @CommaChar + C1.col2

--select *

from #tmp2 C2

inner join #tmp1 C1

on C2.col1 = c1.col1

and C2.Orig_SeqNo < C1.SeqNo

where C1.SeqNo = (select min(SeqNo)

from #tmp1 C1a

where C1a.col1 = c2.col1

and C1a.SeqNo > C2.Orig_SeqNo)

select @ctr = @@rowcount

set @CommaChar = ','

--print @ctr

end

go

select *

from #tmp1

go

select *

from #tmp2

go

drop table #tmp1

drop table #tmp2

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 2

Here is an another approach:

create table #temp(Id int, Name char(3))

create table #temp2 (Id int, Name varchar(50))

insert into #temp values(1, 'Abc')

insert into #temp values(1, 'xyz')

insert into #temp values(1, 'PQr')

insert into #temp values(2, 'Mlo')

insert into #temp values(2, 'stp')

insert into #temp values(3, 'a12')

declare @STR varchar(50), @old_id int, @id int, @name char(3)

declare C cursor for select id, name from #temp

open C

fetch C into @id, @name

set @STR = ''

set @old_id = @id

while @@fetch_status = 0

begin

if @old_id = @id

if @STR = ''

set @STR = @name

else

set @STR = @STR + ',' + @name

else

begin

insert into #temp2 values(@old_id, @STR)

set @STR = @name

set @old_id = @id

end

fetch C into @id, @name

end

insert into #temp2 values(@id, @STR)

close C

deallocate C

select * from #temp2

Bye

Gabor

Bye
Gabor

Jonathan SSC-Insane Points: 20427 More actions · Answer 3

Or, if you can stand yet another way:



CREATE FUNCTION dbo.f_ListTemp(@id int)

RETURNS varchar(8000) BEGIN

DECLARE @list varchar(8000)

SELECT @list = ISNULL(@list + ',','') + Name

FROM Temp

WHERE Id = @id

RETURN @list END

 

SELECT Id, dbo.f_ListTemp(Id)

FROM

(SELECT DISTINCT Id

 FROM Temp) d

ORDER BY Id

Although I would encourage you to accomplish this sort of thing in the front-end, not the back-end.

--Jonathan

Calvin Lawson SSChampion Points: 11030 More actions · Answer 4

Johnathan,

Word...that's the ticket.

rohitkgupta, Johnathan's method of creating csv's in the way to do it, no question (although a buddy of mine and I have argued about the use of isnull. I think it's cleaner this way, but he likes to initialize the variable instead). All other methods I've seen are sub-optimal (by far).

Good stuff.

Signature is NULL

ThomasH SSCertifiable Points: 7437 More actions · Answer 5

Could this help....

-- Make Test "Data"

Select 1 as Num, 'Abc' as Data into #Temp

UNION ALL SELECT 1, 'xyz'

UNION ALL SELECT 1, 'PQr'

UNION ALL SELECT 2, 'Mlo'

UNION ALL SELECT 2, 'stp'

UNION ALL SELECT 3, 'a12'

-- Create "Agg" table

Select Num, convert(Varchar(100), '') as AggData

into #TempAgg From #Temp Group by Num

While @@RowCount <> 0 -- @@RowCount > 0 from above statement & until all data is agg'ed

Update Agg Set AggData = IsNull(AggData + ',', '') + Data

From #TempAgg Agg

join #Temp T on Agg.Num = T.Num and CharIndex(T.Data, Agg.AggData) = 0

Select * from #TempAgg

-- Cleanup

Drop Table #TempAgg

Drop Table #Temp

Once you understand the BITs, all the pieces come together

Calvin Lawson SSChampion Points: 11030 More actions · Answer 6

Thomas,

Your method works, but there's no need for a While loop. It works like this:

 

declare @var varchar(8000)



select @var = isnull(@var + ',', '') + col1

from 

(

select 'A' col1

union all

select 'B'

union all

select 'C'

union all

select 'D'

) d



select @var

Signature is NULL

Rohit @ SQL SSC Eights! Points: 938 More actions · Answer 7

Thank you all for your valuable responses

Jonathan' s Method has solved my problem.

Thank you again

jonathan

Rohit