/* Anith Sen's great achievement, in his article 'Concatenating Row Values in Transact-SQL' http://www.simple-talk.com/sql/t-sql-programming/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 order by productname
/*
...can be grouped like this...

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 my contribution, 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
/*
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.*/

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)