avoiding cursors

Question

Post reply

avoiding cursors

JB-117464

Ten Centuries

Points: 1279
More actions
September 19, 2006 at 7:10 am

#170427

Hi

I have had a request to return a comma delimited string of categories for each customerID from the following table. I can do this easily with a cursor or a function/procedure with loops but i think this will be very slow. There are 200-1000 categories and millions of customers. The categories table is several hundred million.

CREATE

TABLE [dbo].[Categories](

   [CustomerID] [int] NOT NULL,

   [Category] [char]

(3) NOT NULL,

   [Item_Count] [int]

NULL,

   CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED

   (

      [Customer ID]

ASC,

      [Product Category]

ASC

   )

)

I need to return a distinct list of CustomerID followed by a long delimited string of categories that the customer has purchased products from. e.g.

"CustomerID","CatList"                        "00000001","000,001,002,999"

Should i be using recursion, procedural loops or something else? I have already tried convincing the programmers that running select queries on my table is a better idea but to no avail

Viewing 4 posts - 1 through 3 (of 3 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Why can't the programmers do that client side??? It's a very simple loop to code and present.

Anyways if you have to do it server side create a temp table with only the customerID and a catlist varchar(8000) = NULL. Then run something like this (make sure that CONCAT_NULL_YIELDS_NULL is ON) :

Update Tmp SET Tmp.CatList = COALESCE(Tmp.CatList + ',' + C.CatID, C.CatID + ',')

from temp tmp inner join dbo.Categories C on Tmp.CustID = C.CustID

This is untested but the logic is there.

JB-117464 Ten Centuries Points: 1279 More actions · Answer 2

Thanks for the advice. I modified the first line of your code to;

Update Tmp SET Tmp.CatList = COALESCE(Tmp.CatList + ',' + C.CatID, ',') + C.CatID

and it worked great. In the end i decided to go with a function (below) so i could create a view and not create any more data on my sql box (its a bit short on space)!

CREATE

FUNCTION dbo.udf_CategoryList(@CustID int)

RETURNS

varchar(max)

AS

BEGIN

Declare

@List varchar(max)

set

@List = ''

select @List = @List + [CatID] + ','

from