1

I am iterating through a dataset of products looking for SKUs where the initial 6 characters of the product name with the sku @itemno are identical, then concatenating them into a CSV string to be used as part of a dataset to upload into an ecommerce platform. Here is the relevant operation:

DECLARE c1 CURSOR READ_ONLY
FOR
SELECT sku FROM tbl WHERE name LIKE (SELECT LEFT(name,6) FROM tbl WHERE sku = @itemno) + '%'

OPEN c1
FETCH NEXT FROM c1
INTO @c1
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @associated = @associated + @c1 + ','
    FETCH NEXT FROM c1
    INTO @c1
END

As you can imagine this is very slow considering the dataset size of 15,000+ records. Surely there is a more elegant way to do this using PIVOT or something?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Drew
  • 11
  • 1

2 Answers2

3

SQL Server 2005 onwards:

select 
  stuff( 
     (select ', ' + sku  
     from tbl 
     WHERE name LIKE (SELECT LEFT(name,6) FROM tbl WHERE sku = @itemno) + '%' 
     for xml path('') 
     ) 
   , 1, 2, '') as namelist;
Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
3

Try something like

DECLARE @Val VARCHAR(MAX)
SELECT  @Val = COALESCE(@Val + ',','') + sku 
FROM    tbl 
WHERE   name LIKE   (
                        SELECT  LEFT(name,6) 
                        FROM    tbl 
                        WHERE   sku = @itemno) + '%'
Adriaan Stander
  • 162,879
  • 31
  • 289
  • 284
  • +1 to you too. The stuff/xml approach seemed a little to much for a single value return. I do use it for grouping though X-) – Adriaan Stander Dec 16 '10 at 07:46
  • This question is answered here: http://stackoverflow.com/questions/455423/how-to-turn-one-column-of-a-table-into-a-csv-string-in-sql-server-without-using-a – Drew Dec 16 '10 at 08:36