Sql Query Question (How to Combine several rows into one row)

I have two tables one table contains order information OrderNumber, ItemNumber, ItemDescription and things like that. The other table contains Shipment information, Box Number
Date Shipped and Time Shipped. If a Item in Table one has been shipped in multiple boxes say 3 for example I do not want to get three rows returned with the only difference being a box number I want to get One row returned and it contain all three box numbers and the Latest Date Shipped

Here is what I get now

Item Number                             Box Number         Date Shipped
   55532                                         234                    4/3/2009
   55532                                         548                    4/12/2009
   55532                                         762                    5/1/2009

Here is what I want to get

ItemNumber                              BoxNumber                  DateShipped
   55532                                      234, 548, 762               5/152009

How do I write my query statement to get the results that I want

I am using Microsoft Sql 2008
WesleyAlgeeAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
SELECT ItemNumber, Box =   (SELECT cast(BoxNumber as varchar ) +',' FROM urTable t where  t.ItemNumber = b.itemNumber for XML path('') )
,MAX (ShipDate)
FROM urTable b
GROUP BY ItemNumber
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
ralmadaCommented:
Please give this a try. Change table names accordingly.

SELECT 
	a.ItemNumber
	BoxNumber = REPLACE( 
	( 
	SELECT 
	BoxNumber AS [data()] 
	FROM 
	shipmenttable c 
	WHERE 
	c.ItemNumber = b.ItemNumber
	ORDER BY 
	c.ItemNumber 
	FOR XML PATH ('') 
	), ' ', ','),
 
	Max(b.DateShipped)
FROM 
Ordertable a
Inner join shipmenttable b on a.ItemNumber = b.ItemNumber
group by a.ItemNumber

Open in new window

0
 
WesleyAlgeeAuthor Commented:
ok I will give these a try I will not be able to work on it again until tomorrow
0
 
WesleyAlgeeAuthor Commented:
I am sorry it took me so long to respond something critical came up that I had to give my attention to
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.