Coalesce on a table without cursoring up?

Question

Post reply

Coalesce on a table without cursoring up?

Coriolan

SSCommitted

Points: 1931
More actions
April 5, 2006 at 2:54 pm

#167677

Hi, This is my table with sample data
   ID            Group name
-----------------------------------------
146          ITO
146          7-Eleven
149          PPM Technology
200          Bank of America
200          Chase
200          UMB
I need to coalesce group names by ID. So the expected results should be
    ID           Group name
-----------------------------------------
146          ITO, 7-Eleven
149          PPM Technology
200          Bank of America, Chase, UMB
Currently I am using a cursor with a coalesce to build the comma separated group name string. My current SQL:
Open Cursor on table1
FETCH NEXT FROM c1
INTO @ENT_ResourceUniqueID
WHILE @@FETCH_STATUS = 0
        BEGIN
              SELECT @OC_Names = COALESCE(@OC_Names + ', ', '') +
              OC_NAME FROM table1 where ENT_ResourceUniqueID = @ENT_ResourceUniqueID               ....
         end
FETCH NEXT FROM c1
INTO @ENT_ResourceUniqueID... etc....
Though it works, it has proven to be time consuming. Plus, I have to use the right function to get rid of the first ", " in each resulting string.
How can I rewrite this without using a cursor? An ID can have a max of 100 groups. Group names are 255-chars each.
Many thanks in advance.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

See the following post.

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=271272

Coriolan SSCommitted Points: 1931 More actions · Answer 2

Thanks for the pointer. My table is a temporary table and I believe we cannot access temporary tables

from within a function.

I will try to see if I can use a table variable instead of temporary table.

Amit Lohia SSCertifiable Points: 6104 More actions · Answer 3

You have to use some kind of loop, whether in form of cursor or loop

my experience loop are faster than cursors. You can also use case instead of using right to get rid of extra ",". I do not know which one will be faster

DECLARE @MaxId INT

DECLARE @StartId INT

DECLARE @Groups Varchar(8000)

SELECT @StartId = 0 ,@MaxId = MAX(ID) FROM A

WHILE @StartId <= @MaxId

BEGIN

SET @Groups = NULL

SELECT @Groups = COALESCE(@Groups,'') + ',' + OC_NAME

FROM table1 WHERE ENT_ResourceUniqueID = @StartId

SET @StartId = @StartId + 1

IF @Groups IS NOT NULL

BEGIN

SELECT @StartId ,@Groups

END

Kindest Regards,

Amit Lohia

Coriolan SSCommitted Points: 1931 More actions · Answer 4

Coriolan

SSCommitted

Points: 1931

April 5, 2006 at 7:35 pm

#631131

Thanks, Amit. I will give it a try.

Coriolan SSCommitted Points: 1931 More actions · Answer 5

Thanks to both people who responded. I found this link http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/2d85bf366dd9e73e and it depicted 5 ways of doing what I am trying to do. One of which is the UDF as suggested in the first response to my questions and another which is a while loop as described in the second response.

Again, many thanks.