/* Anith Sen's achievement, in his article 'Concatenating Row Values in Transact-SQL' has been to list and illustrate the many ways of producing a 'grouping' or concatenation of row values so that the result of this... */
SELECT CategoryId, ProductName FROM Northwind..Products
/*
...can be 'grouped', in a far more human-readable form, like this. (in fact, when you first talk about SQL Grouping, many people think that this is what a GROUP BY does)...

CATEGORYid  Product List
1           Chai,Chang,Chartreuse verte,Côte de Blaye,Guaraná Fantástica,.....
2           Aniseed Syrup,Chef Anton's Cajun Seasoning,Chef Anton's Gumb.....
3           Chocolade,Gumbär Gummibärchen,Maxilaku,NuNuCa Nuß-Nougat-Cre.....
4           Camembert Pierrot,Flotemysost,Geitost,Gorgonzola Telino,Gudb.....
5           Filo Mix,Gnocchi di nonna Alice,Gustaf's Knäckebröd,Ravioli .....
6           Alice Mutton,Mishi Kobe Niku,Pâté chinois,Perth Pasties,Thür.....
7           Longlife Tofu,Manjimup Dried Apples,Rössle Sauerkraut,Tofu,U.....
8           Boston Crab Meat,Carnarvon Tigers,Escargots de Bourgogne,Gra.....

Here, of course is one of my contribution to the collection of solutions, though Anith documents simpler methods  */

DECLARE @list VARCHAR(MAX)
SELECT @List=COALESCE(@list+',','')
   +'|'+CONVERT(VARCHAR(5),CategoryID)
   +'|'+ productName+'|'+CONVERT(VARCHAR(5),CategoryID) +'|'
  FROM northwind..products ORDER BY categoryID,productName

SELECT CATEGORYid,
   [Product List] =LEFT(members,CHARINDEX(',|',members+',|')-1) FROM
(
SELECT categoryID, 'members'=
        REPLACE(
          SUBSTRING(list,
             CHARINDEX('|'+CONVERT(VARCHAR(5),CategoryID)+'|',list),8000),
           '|'+CONVERT(VARCHAR(5),CategoryID)+'|', '')
FROM (SELECT 'list'=@List)f
CROSS JOIN (
   SELECT categoryID FROM northwind..products GROUP BY categoryID
   ) Categories
)g

/* here is another approach that occurred to me, based on the quirky update of Transact SQL. I posted it as a comment to Anith's article but I'll repeat it here in case you haven't seen it */

DECLARE @accumulation VARCHAR(7000),
--variable used for accumulating lists
@CategoryID INT
--variable used for keeping tabs on the GROUPING id

DECLARE @grouping TABLE--temporary table
(
   MyID INT IDENTITY(1, 1) PRIMARY KEY,
   CategoryID INT,
   ProductName VARCHAR(255),
   accumulation VARCHAR(7000)--used to collect the list
)
INSERT INTO @Grouping --insert raw result you want a GROUPING of
(CategoryID, ProductName)
   SELECT CategoryID, ProductName
       FROM Northwind..Products
       ORDER BY CategoryID, ProductName

UPDATE @grouping --and update the table, doing the accumulation.
   SET @Accumulation = [accumulation]
       = COALESCE(
               CASE WHEN CategoryID <> COALESCE(@CategoryID, 0)
               THEN '' + productName
               ELSE LEFT(@Accumulation + ',' + productName, 7000)
           END,
           ''),
       @CategoryID = CategoryID

SELECT CategoryID, MAX(accumulation)
   FROM @grouping
   GROUP BY CategoryID
   ORDER BY CategoryID /*

Now, how do you reverse the process so that, from the concatenated row values, you get back to the original result? i.e. This:

CategoryId  ProductName
----------- ----------------------------------------
6           Alice Mutton
2           Aniseed Syrup
8           Boston Crab Meat
4           Camembert Pierrot
8           Carnarvon Tigers
1           Chai
1           Chang
1           Chartreuse verte
2           Chef Anton's Cajun Seasoning
2           Chef Anton's Gumbo Mix
.......
.......(etc)


Hmm... I wonder. Lots of RBAR? Well, it is probably going to be a combination, but here is my stab at a solution. I'd be tickled pink if you can think of an easier way of doing it.

Firstly, lets set up the test data from dear old Northwind.
*/
CREATE TABLE #testdata(Category_ID INT, ProductList VARCHAR(MAX))
DECLARE @list VARCHAR(MAX)

SELECT @List=COALESCE(@list+',','')
   +'|'+CONVERT(VARCHAR(5),CategoryID)
   +'|'+ productName+'|'+CONVERT(VARCHAR(5),CategoryID) +'|'
  FROM northwind..products ORDER BY categoryID,productName

INSERT INTO #TestData
   SELECT CATEGORYid,
      [Product List]=LEFT(members,CHARINDEX(',|',members+',|')-1) FROM
   (
   SELECT categoryID, 'members'=
           REPLACE(
             SUBSTRING(list,
                CHARINDEX('|'+CONVERT(VARCHAR(5),CategoryID)+'|',list),8000),
              '|'+CONVERT(VARCHAR(5),CategoryID)+'|', '')
   FROM (SELECT 'list'=@List)f
   CROSS JOIN (
      SELECT categoryID FROM northwind..products GROUP BY categoryID
      )Categories
   )g
/*
Ok. Now you have the data, it is time to see if you can come up with a solution that performs well. Here is my attempt. You'll notice that I'm using the trick of TSQL that allows you variables in update statements. It is an important weapon in avoiding iterative solutions. */



DECLARE @tempdata TABLE (Category_ID INT, ProductList VARCHAR(MAX))
DECLARE @dynamicCode VARCHAR(MAX)
INSERT INTO @tempdata SELECT * FROM #TESTDATA
WHILE EXISTS (SELECT 1 FROM @tempdata WHERE LEN(productList) >0)
UPDATE @tempData SET
   @dynamicCode=COALESCE( @dynamicCode+'
    union all ','')+'
Select [CategoryId]='+CONVERT(VARCHAR(5),Category_ID)+',[ProductName]='''
           +RTRIM(REPLACE(LEFT(productlist,CHARINDEX(',',productList+',')-1),'''',''''''))+'''',
   productList=RIGHT(productList+' '
               ,LEN(productList+'x')-CHARINDEX(',',productList+',')
               )
   WHERE LEN(ProductList) >0
EXECUTE (@DynamicCode)


/* the source file can be downloaded from Here    */