T Hoecherl
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Brilliant. Thank you.
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