asked on

Convert Table to Delimited List

I have a table that looks like this: http://screencast.com/t/rcPtDTfK05

I'd like to create a view, based on this table that looks like this: http://screencast.com/t/eK699ixc226V

Using MS SQL 2005

strickdd

I am unable to load the images you linked to, can you attach them to the post directly?

Steven Graff

ASKER

How would I do that? "Paste" or ctrl-v doesn't seem to work.

sachinpatil10d

You will have to use Pivot
reffer this links

http://msdn.microsoft.com/en-us/library/ms177410.aspx

http://blog.sqlauthority.com/2008/05/22/sql-server-pivot-table-example/

sachinpatil10d

run this query

SELECT p1.CustNo,
       ( SELECT COlor + ','
           FROM TblName p2
          WHERE p2.CustNo = p1.CustNo
          ORDER BY CustNo
            FOR XML PATH('') ) AS Products
      FROM TblName p1
      GROUP BY CustNo ;

Open in new window

ASKER CERTIFIED SOLUTION

sachinpatil10d

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

strickdd

At the bottom, below the comment box, there is a link to add "File", "Code", "Image", or "Screenshot"

Steven Graff

ASKER

Final incarnation (to remove extra spaces between color and comma, using rtrim):

select CustNo, left(COlors,len(COlors)-1) COlors from
(SELECT p1.CustNo,
( SELECT rtrim(COlor) + ', '
FROM products p2
WHERE p2.CustNo = p1.CustNo
ORDER BY CustNo
FOR XML PATH('') ) AS COlors
FROM products p1
GROUP BY CustNo ) t;