[AccessD] OT: T-SQL 2005 - Building lookup rows from string...

Hi All,

Using the following T-SQL in MS SQL 2005:

select 
REPLACE(
(
  SELECT 
	Cast(CategoryId as nvarchar(10)) + ',' + 
	CategoryName + ',' 
	FROM Categories
	ORDER BY CategoryName
	FOR XML PATH('')
) + '-',
',-','')
as Categories

You can get the string

1,Beverages,2,Condiments,3,Confections,4,Dairy Products,5,Grains/Cereals,6,Meat/Poultry,7,Produce,8,Seafood

Questions:
1. If you know better way to get the same string please post it here (please do not spend time looking for it if youy do not have prompt solution from your experience - looking for other solution could be time consuming).

2. Do you know similar T-SQL only based way to split the above string to get a table:

CategoryId CategoryName
1 Beverages
2 Condiments
...
8 Seafood

? (probably UDF should be used)


N.B. The above technique introduced here (http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/)coudl be used to make cross-tab queries in T-SQL using dynamic SQL...

Thank you.

--
Shamil