Need help in combining duplicate columns into new table

Transact-SQL
1

Howdy
I have a table with a column containing a duplicate values in order_no, and I would like to combine associated values in tracking_no into perhaps a new table, where there will be only ONE row with the order_no, but with tracking_no values comma separated.

Existing Table Data:
order_no tracking_no
1245721 1Z0310590354679691
1245721 1Z0310590354500300
1245721 1Z0310590353801657

Desired Table Data:
1245721 1Z0310590354679691,1Z0310590354500300,1Z0310590353801657

I'm struggling with the logic ...
I can find the duplicates using for example:

SELECT order_no, Count(*)
FROM shipping_data
group by order_no
HAVING ( COUNT(order_no) > 1 )

But don't know how to use those results properly..
I am assuming I should have another table to hold the "modified" results?

Many thanks for any help /pointers in the right direction!
Rich

2 
SELECT
	a.order_no,
	STUFF(b.tracking_numbers,1,1,'') AS TrackingNumbers
FROM
	(SELECT DISTINCT order_no FROM shipping_data) AS a
	CROSS APPLY
	(
		SELECT ','+tracking_no
		FROM shipping_data c
		WHERE c.order_no = a.order_no
		ORDER BY tracking_no
		FOR XML PATH('')
	)b(tracking_numbers);
3

holy crap man, that's awesome
thanks...
now I must dissect & learn...

4

if it wouldn't require much time or effort, I'd press my luck further by asking how I might
add in additional columns that exist in the shipping_data table...

If not, no worries: you've come through already

well done sir,
Rich

5

You can add other columns - but if for a given order_no, the other columns have different values in different rows, then do you want to show all such rows? Or do you want to show just one row for a given order_no? If you want to show only one row for a given order_no, how do you pick which value from the other column to show?

Assuming you want to show a row for each unique combination of order_no, OtherCol1 and OtherCol2,

  SELECT
	a.order_no,
	a.otherCol1,
	a.otherCol2
	STUFF(b.tracking_numbers,1,1,'') AS TrackingNumbers
FROM
	(SELECT DISTINCT order_no,otherCol1,otherCol2 FROM shipping_data) AS a
	CROSS APPLY
	(
		SELECT ','+tracking_no
		FROM shipping_data c
		WHERE c.order_no = a.order_no
		ORDER BY tracking_no
		FOR XML PATH('')
	)b(tracking_numbers);