Query to merge data from two rows into one

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query to merge data from two rows into one

Author

Topic

knox203
Starting Member

31 Posts

Posted - 2009-05-05 : 17:24:03

Hey everyone,

I have a query that spans multiple tables, of which contains information for a pickup address, city, customer, etc... as well as the delivery information. I need to merge the delivery information into the same row as the pickup information. I don't think I'm making much sense, so I'll show you what I'm working with. Here's my current query:

SELECT     OrderMain.OrderNumber, OrderMain.CreateDate, OrderMain.OrderDate, OrderMain.Service, OrderMain.Pieces, Customer.CustomerCode, 
                      Customer.Name AS CustomerName, Stop.Name, Stop.Address, Stop.City, Stop.State, 
                      Stop.Zip, OrderStop.StopType
FROM         OrderMain INNER JOIN
                      Customer ON OrderMain.CustomerID = Customer.CustomerID INNER JOIN
                      OrderStop ON OrderMain.OrderID = OrderStop.OrderID INNER JOIN
                      Stop ON OrderStop.StopID = Stop.StopID

Which returns:

132	11/19/2007 01:34:00	11/19/2007 12:00:00	Same_Day  	1.00	(Pickup Customer Code)	(Pickup Customer Name)	(Pickup Address) 	(Pickup City)	(Pickup State)	(Pickup Zip)	(Stop Type / Pickup)
132	11/19/2007 01:34:00	11/19/2007 12:00:00	Same_Day  	1.00	(Delivery Customer Code)	(Delivery Customer Name)	(Delivery Address) 	(Delivery City)	(Delivery State)	(Delivery Zip)	(Stop Type / Delivery)

I would like it to look like this:

132	11/19/2007 01:34:00	11/19/2007 12:00:00	Same_Day  	1.00	(Pickup Customer Code)	(Pickup Customer Name)	(Pickup Address) 	(Pickup City)	(Pickup State)	(Pickup Zip)	(Delivery Customer Code)	(Delivery Customer Name)	(Delivery Address) 	(Delivery City)	(Delivery State)	(Delivery Zip)

I've done something similar to this in the past... but unfortunately forgot the query

Can anyone provide me with a direction? Thanks!!

- Adam

DavidD
Yak Posting Veteran

73 Posts

Posted - 2009-05-05 : 18:48:47

Hi Adam,

You need to seperate out your pickup and delivery data into seperate subqueries. Something like

SELECT O.OrderNumber, O.CreateDate, O.OrderDate, O.Service, O.Pieces, C1.CustomerCode as DeliveryCode, C2.CustomerCode as pickupCode, c1.CustomerName as DeliveryCustName, c2.CustomerName AS PickupCustName, S1.Name as DeliveryName, s2.Name as PickupName etc
FROM OrderMain o INNER JOIN
(select * from Customer where customer = deliverycustomer) c1 ON O.CustomerID = C1.CustomerID INNER JOIN
(select * from Customer where customer = pickupcustomer) c2 ON O.CustomerID = C2.CustomerID INNER JOIN
OrderStop os ON O.OrderID = Os.OrderID INNER JOIN (select * from stop where stop = delivery) s1
ON Os.OrderID = s1.OrderID INNER JOIN
INNER JOIN (select * from stop where stop = delivery) s2
ON Os.OrderID = s2.OrderID

I dont know the fields you would use to seperate the subqueries, but you should be able to work it out.

Regards
David

knox203
Starting Member

31 Posts

Posted - 2009-05-05 : 19:45:24

David,

Thanks very much for your reply, I was able to get it figured out based on that query you sent me! It was quite a bit simpler than I thought it would be (I forgot about you, Sub Queries!!), here's the final query in case anyone is interested:

SELECT	O.OrderNumber, O.Service, O.AmountCharged, p1.PickupName, p1.PickupAddress, p1.PickupCity, p1.PickupState, p1.PickupZip, d1.DeliveryName, d1.DeliveryAddress, d1.DeliveryCity, d1.DeliveryState, d1.DeliveryZip
FROM	dbo.OrderMain O INNER JOIN
(	
	SELECT	OrderMain.OrderNumber, Stop.Name AS PickupName, Stop.Address AS PickupAddress, Stop.City AS PickupCity, Stop.State AS PickupState, Stop.Zip AS PickupZip
	FROM		dbo.OrderMain INNER JOIN
			dbo.Customer ON OrderMain.CustomerID = Customer.CustomerID INNER JOIN
			dbo.OrderStop ON OrderMain.OrderID = OrderStop.OrderID INNER JOIN
			dbo.Stop ON OrderStop.StopID = Stop.StopID
	WHERE		OrderStop.StopType = 'P'
) p1
ON O.OrderNumber = p1.OrderNumber INNER JOIN
(	
	SELECT	OrderMain.OrderNumber, Stop.Name AS DeliveryName, Stop.Address AS DeliveryAddress, Stop.City AS DeliveryCity, Stop.State AS DeliveryState, Stop.Zip AS DeliveryZip
	FROM		dbo.OrderMain INNER JOIN
			dbo.Customer ON OrderMain.CustomerID = Customer.CustomerID INNER JOIN
			dbo.OrderStop ON OrderMain.OrderID = OrderStop.OrderID INNER JOIN
			dbo.Stop ON OrderStop.StopID = Stop.StopID
	WHERE		OrderStop.StopType = 'D'
) d1
ON O.OrderNumber = d1.OrderNumber

Thanks again,
- Adam

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-05 : 22:27:12

or just change the 2 INNER JOIN to


	SELECT		OrderMain.OrderNumber, 
			MAX(CASE WHEN OrderStop.StopType = 'P' THEN Stop.Name END) AS PickupName, 
			MAX(CASE WHEN OrderStop.StopType = 'P' THEN Stop.Address END) AS PickupAddress, 
			MAX(CASE WHEN OrderStop.StopType = 'P' THEN Stop.City END) AS PickupCity, 
			MAX(CASE WHEN OrderStop.StopType = 'P' THEN Stop.State END) AS PickupState, 
			MAX(CASE WHEN OrderStop.StopType = 'P' THEN Stop.Zip END) AS PickupZip,
			MAX(CASE WHEN OrderStop.StopType = 'D' THEN Stop.Name END) AS DeliveryName, 
			MAX(CASE WHEN OrderStop.StopType = 'D' THEN Stop.Address END) AS DeliveryAddress, 
			MAX(CASE WHEN OrderStop.StopType = 'D' THEN Stop.City END) AS DeliveryCity, 
			MAX(CASE WHEN OrderStop.StopType = 'D' THEN Stop.State END) AS DeliveryState, 
			MAX(CASE WHEN OrderStop.StopType = 'D' THEN Stop.Zip END) AS DeliveryZip
	FROM		dbo.OrderMain INNER JOIN
			dbo.Customer ON OrderMain.CustomerID = Customer.CustomerID INNER JOIN
			dbo.OrderStop ON OrderMain.OrderID = OrderStop.OrderID INNER JOIN
			dbo.Stop ON OrderStop.StopID = Stop.StopID
	GROUP BY	OrderMain.OrderNumber

KH
[spoiler]Time is always against us[/spoiler]

knox203
Starting Member

31 Posts

Posted - 2009-05-06 : 12:37:37

Right you are, khtan! That looks to be much simpler... gotta go with the most efficient query, thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources