SQL View to concatenate multiple lines into one

I'm trying to create a view using SQL Server Management Studio 2008 R2.  I have sales records in a table which have assigned tracking numbers.  There may be up to 5 tracking numbers for each order, so a portion of the table looks like this:

SOPNUMBE                    SOPTYPE      Tracking_Number      
INV-500001                 3      15461d3f2s489fs4                               
INV-500001                 3      sdf5s4df89s4d3f2                               
INV-500002                 3      068077960260939                                
INV-500003                 3      1ZR45F660354550566                             
INV-500004                 3      068077960260816                                

As you can see, INV-500001 is in the table twice because there are 2 tracking numbers.  In the view, I want that record to appear like this:

SOPNUMBE                    SOPTYPE      Tracking_Number      
INV-500001                 3      15461d3f2s489fs4, sdf5s4df89s4d3f2    

with the tracking numbers concatenated into one field.  I tried using a self join, but that concatenated the numbers twice, switching the order of the tracking numbers in the concatenation, and resulted in four lines, rather than one.

I could do this with a cursor, but obviously that is out of the question for a view.  Any ideas on how I might be able to accomplish this?

T
T HoecherlDeveloperAsked:
Who is Participating?
 
HainKurtSr. System AnalystCommented:
try this:

select distinct sopnumbe, soptype, (
SELECT Tracking_Number + ', ' FROM sop s2 WHERE s1.SOPNUMBE=s2.SOPNUMBE FOR XML Path('')
) Tracking_Numbers
from sop s1
0
 
Ephraim WangoyaCommented:

SELECT SOPNUMBE,                  SOPTYPE,
        STUFF(
            (
            SELECT ', ' + Tracking_Number
            FROM Table1 B
            WHERE SOPNUMBE = A.SOPNUMBE
            FOR XML PATH('')
            ), 1, 1, ''
        ) As  Tracking_Number
from Table1 A
group by SOPNUMBE, SOPTYPE
0
 
T HoecherlDeveloperAuthor Commented:
Brilliant.  Thank you.
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.