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

LVL 11
stevengraffAsked:
Who is Participating?
 
sachinpatil10dCommented:
output eliminating last comma


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

Open in new window

0
 
strickddCommented:
I am unable to load the images you linked to, can you attach them to the post directly?
0
 
stevengraffAuthor Commented:
How would I do that? "Paste" or ctrl-v doesn't seem to work.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
sachinpatil10dCommented:
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

0
 
strickddCommented:
At the bottom, below the comment box, there is a link to add "File", "Code", "Image", or "Screenshot"
0
 
stevengraffAuthor Commented:
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;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.