Hi All

I have a table that contains a comma separated list of products that I want
to pivot so that they each product is listed individually. Below is a sample
of the table and the data as well as how I am trying to return the data. Any
assistance anyone can provide would be appreciated.

CREATE TABLE Products
(
i INT,
ProdName VARCHAR(1000)
)

INSERT Products SELECT 1, 'Books, Magazines'
INSERT Products SELECT 2, 'Homewears'
INSERT Products SELECT 2, 'Kitchenware, Hardware, Manchester'

i ProdName
---- ------------
1 Books
1 Magazines
2 Homewears
3 Kitchenware
3 Hardware
3 Manchester

The right solution is to avoid having multiple values in a single column.
Have a key explicitly declared and make sure your queries can be expressed
as simple SELECT statements.

If this is something you have little control over, there are several options
for list parsing. Some of them can be found at:
www.projectdmx.com/tsql/sqlarrays.aspx

--
Anith

Take a look at this very nice function by Dejan Sarka:
http://solidqualitylearning.com/Blogs/dejan/archive/2004/10/22/200.aspx


ML

---