Merge multiple rows into single row

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 2008 Forums

Transact-SQL (2008)

Merge multiple rows into single row

Author

Topic

epic monkey
Starting Member

4 Posts

Posted - 2011-11-25 : 05:29:28

Hey guys,
I have a table with multiple transactions/rows for customers orders. The table is purely a transactional recording of what we have received and by no means is it a single view of the current status of an order.

I need to roll all rows for each order number into one single row containing the most recent data for each column. For example: This is how the data sits in the transactional table:

OrderID,CustID,ItemNumber,ItemCategory,OrderStatus,CreateDate,Title,FirstName,LastName
1,3,777,99,NULL,2011-11-10,Mr,Joe,Blogs
1,3,777,99,Pending,2011-11-11,NULL,NULL,NULL
1,3,777,99,Fulfilled,2011-11-15,NULL,NULL,NULL

This is how I need it to be pulled back
OrderID,CustID,ItemNumber,ItemCategory,OrderStatus,CreateDate,Title,FirstName,LastName
1,3,777,99,Fulfilled,2011-11-15,Mr,Joe,Blogs

So if a more recent row has NULL values then populate from a previous row where the value is not null but always use the most recent not null values.
It might be worth mentioning that there is never a definitive number or rows per order number, there could be only 1 row, there could be 10+

What is the best way to go about doing this?

Thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-25 : 05:50:28

something like
;with cte as
(
select *, seq = row_number() over (partition by orderID, CustID, ItemNumber, ItemCategory order by CreateDate desc from tbl
) ,
cte2 as
(
select * from cte t1 where seq = (select max(seq) cte t2 where t1.orderID = t2.orderID and t1.CustID = t2.CustID and t1.ItemNumber = t2.ItemNumber and t1.ItemCategory = t2.ItemCategory)
union all
select orderID, CustID, ItemNumber, ItemCategory, OrderStatus = coalesce(t2.OrderStatus, t1.OrderStatus), t2.CreateDate, coalesce(t2.Title, t1.Title), ...
from cte2 t1 join cte t2 on t1.orderID = t2.orderID and t1.CustID = t2.CustID and t1.ItemNumber = t2.ItemNumber and t1.ItemCategory = t2.ItemCategory
and t1.seq = t2.seq-1 and t2.seq > 1
)
select * from cte2 where seq = 1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

epic monkey
Starting Member

4 Posts

Posted - 2011-11-25 : 10:49:54

Thanks for the help NigelRivett. I've taken what you advised and adapted to the table in question but still have the problem of multiple rows being displayed for the same orderNumber & CustID combination. Below is the exact code I am using:




;WITH CTE (
	OrderNumber,
	Cust_ID,
	Vehicle_ID,
	Company_id,
	DealerCode,
	RegistrationNumber,
	EndItemCode,
	NMGBSalesType,
	AnnualMileage,
	SourceOfFinance,
	FinanceTerm,
	IRD,
	PreviousVehicle_ID,
	PreviousVehicleMake,
	PreviousVehicleModel,
	PreviousVehicleRegNumber,
	PreviousVehicleYearsOwned,
	FleetCode,
	CompanyName,
	C_address_id,
	CompanyPhone,
	FleetContactTitle,
	FleetContactFirstName,
	FleetContactSurname,
	FleetContactJobTitle,
	OrderCreationDate,
	ExtractDate,
	REQUESTED_DELIVERY_DATE,
	Customer_Offer_Code,
	delivery_status,
	SEQ ) 
AS
(
SELECT 
	*, 
	SEQ = ROW_NUMBER() 
		OVER (PARTITION BY 	
				OrderNumber,
				Cust_ID,
				Vehicle_ID,
				Company_id,
				DealerCode,
				RegistrationNumber,
				EndItemCode,
				NMGBSalesType,
				AnnualMileage,
				SourceOfFinance,
				FinanceTerm,
				IRD,
				PreviousVehicle_ID,
				PreviousVehicleMake,
				PreviousVehicleModel,
				PreviousVehicleRegNumber,
				PreviousVehicleYearsOwned,
				FleetCode,
				CompanyName,
				C_address_id,
				CompanyPhone,
				FleetContactTitle,
				FleetContactFirstName,
				FleetContactSurname,
				FleetContactJobTitle,
				OrderCreationDate,
				ExtractDate,
				REQUESTED_DELIVERY_DATE,
				Customer_Offer_Code,
				delivery_status 
			ORDER BY ExtractDate DESC) FROM	OrdersTemp (NOLOCK)	
			
		
) 


, CTE2 
AS
(
SELECT 
	OrderNumber,
	Cust_ID,
	Vehicle_ID,
	Company_id,
	DealerCode,
	RegistrationNumber,
	EndItemCode,
	NMGBSalesType,
	AnnualMileage,
	SourceOfFinance,
	FinanceTerm,
	IRD,
	PreviousVehicle_ID,
	PreviousVehicleMake,
	PreviousVehicleModel,
	PreviousVehicleRegNumber,
	PreviousVehicleYearsOwned,
	FleetCode,
	CompanyName,
	C_address_id,
	CompanyPhone,
	FleetContactTitle,
	FleetContactFirstName,
	FleetContactSurname,
	FleetContactJobTitle,
	OrderCreationDate,
	ExtractDate,
	REQUESTED_DELIVERY_DATE,
	Customer_Offer_Code,
	delivery_status 
FROM 
	CTE T1 
WHERE 
	SEQ = 
		(SELECT MAX(SEQ) FROM CTE T2 
			WHERE 
				t1.OrderNumber = t2.OrderNumber 
			and	t1.Cust_ID = t2.Cust_ID 
			and t1.Vehicle_ID = t2.Vehicle_ID 
		)

		
UNION ALL
SELECT 
	COALESCE(t2.OrderNumber, t1.OrderNumber ),
	COALESCE(t2.Cust_ID, t1.Cust_ID ),
	COALESCE(t2.Vehicle_ID, t1.Vehicle_ID ),
	COALESCE(t2.Company_id, t1.Company_id ),
	COALESCE(t2.DealerCode, t1.DealerCode ),
	COALESCE(t2.RegistrationNumber, t1.RegistrationNumber ),
	COALESCE(t2.EndItemCode, t1.EndItemCode ),
	COALESCE(t2.NMGBSalesType, t1.NMGBSalesType ),
	COALESCE(t2.AnnualMileage, t1.AnnualMileage ),
	COALESCE(t2.SourceOfFinance, t1.SourceOfFinance ),
	COALESCE(t2.FinanceTerm, t1.FinanceTerm ),
	COALESCE(t2.IRD, t1.IRD ),
	COALESCE(t2.PreviousVehicle_ID, t1.PreviousVehicle_ID ),
	COALESCE(t2.PreviousVehicleMake, t1.PreviousVehicleMake ),
	COALESCE(t2.PreviousVehicleModel, t1.PreviousVehicleModel ),
	COALESCE(t2.PreviousVehicleRegNumber, t1.PreviousVehicleRegNumber ),
	COALESCE(t2.PreviousVehicleYearsOwned, t1.PreviousVehicleYearsOwned ),
	COALESCE(t2.FleetCode, t1.FleetCode ),
	COALESCE(t2.CompanyName, t1.CompanyName ),
	COALESCE(t2.C_address_id, t1.C_address_id ),
	COALESCE(t2.CompanyPhone, t1.CompanyPhone ),
	COALESCE(t2.FleetContactTitle, t1.FleetContactTitle ),
	COALESCE(t2.FleetContactFirstName, t1.FleetContactFirstName ),
	COALESCE(t2.FleetContactSurname, t1.FleetContactSurname ),
	COALESCE(t2.FleetContactJobTitle, t1.FleetContactJobTitle ),
	COALESCE(t2.OrderCreationDate, t1.OrderCreationDate ),
	COALESCE(t2.ExtractDate, t1.ExtractDate ),
	COALESCE(t2.REQUESTED_DELIVERY_DATE, t1.REQUESTED_DELIVERY_DATE ),
	COALESCE(t2.Customer_Offer_Code, t1.Customer_Offer_Code ),
	COALESCE(t2.delivery_status, t1.delivery_status )
FROM 
	CTE2 T1 
INNER JOIN cte t2 
	on	t1.OrderNumber = t2.OrderNumber 
	and	t1.Cust_ID = t2.Cust_ID 
	and t1.Vehicle_ID = t2.Vehicle_ID 
)

select	* from CTE2;

I've tried a few different things to get the single rows back but have started to run out of ideas. Any further advice is greatly appreciated.
Thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-25 : 11:11:57

Sorry - I missed the seq from the cte2 resultset.
The idea is that cte numbers all the rows in a partition in reverse order.
cte2 goes through each of those in order taking the value from the next row if it is not null.
seq = 1 will be the last row processed and have the latest data for each column.

You also need to partition on the join columns in the first cte


;WITH CTE (
	OrderNumber,
	Cust_ID,
	Vehicle_ID,
	Company_id,
	DealerCode,
	RegistrationNumber,
	EndItemCode,
	NMGBSalesType,
	AnnualMileage,
	SourceOfFinance,
	FinanceTerm,
	IRD,
	PreviousVehicle_ID,
	PreviousVehicleMake,
	PreviousVehicleModel,
	PreviousVehicleRegNumber,
	PreviousVehicleYearsOwned,
	FleetCode,
	CompanyName,
	C_address_id,
	CompanyPhone,
	FleetContactTitle,
	FleetContactFirstName,
	FleetContactSurname,
	FleetContactJobTitle,
	OrderCreationDate,
	ExtractDate,
	REQUESTED_DELIVERY_DATE,
	Customer_Offer_Code,
	delivery_status,
	SEQ ) 
AS
(
SELECT 
	*, 
	SEQ = ROW_NUMBER() 
		OVER (PARTITION BY 	
				OrderNumber,
				Cust_ID,
				Vehicle_ID
			ORDER BY ExtractDate DESC) FROM	OrdersTemp (NOLOCK)	
			
	
) 
, CTE2 
AS
(
SELECT 
	OrderNumber,
	Cust_ID,
	Vehicle_ID,
	Company_id,
	DealerCode,
	RegistrationNumber,
	EndItemCode,
	NMGBSalesType,
	AnnualMileage,
	SourceOfFinance,
	FinanceTerm,
	IRD,
	PreviousVehicle_ID,
	PreviousVehicleMake,
	PreviousVehicleModel,
	PreviousVehicleRegNumber,
	PreviousVehicleYearsOwned,
	FleetCode,
	CompanyName,
	C_address_id,
	CompanyPhone,
	FleetContactTitle,
	FleetContactFirstName,
	FleetContactSurname,
	FleetContactJobTitle,
	OrderCreationDate,
	ExtractDate,
	REQUESTED_DELIVERY_DATE,
	Customer_Offer_Code,
	delivery_status  ,
	SEQ
FROM 
	CTE T1 
WHERE 
	SEQ = 
		(SELECT MAX(SEQ) FROM CTE T2 
			WHERE 
				t1.OrderNumber = t2.OrderNumber 
			and	t1.Cust_ID = t2.Cust_ID 
			and t1.Vehicle_ID = t2.Vehicle_ID 
		)

		
UNION ALL
SELECT 
	COALESCE(t2.OrderNumber, t1.OrderNumber ),
	COALESCE(t2.Cust_ID, t1.Cust_ID ),
	COALESCE(t2.Vehicle_ID, t1.Vehicle_ID ),
	COALESCE(t2.Company_id, t1.Company_id ),
	COALESCE(t2.DealerCode, t1.DealerCode ),
	COALESCE(t2.RegistrationNumber, t1.RegistrationNumber ),
	COALESCE(t2.EndItemCode, t1.EndItemCode ),
	COALESCE(t2.NMGBSalesType, t1.NMGBSalesType ),
	COALESCE(t2.AnnualMileage, t1.AnnualMileage ),
	COALESCE(t2.SourceOfFinance, t1.SourceOfFinance ),
	COALESCE(t2.FinanceTerm, t1.FinanceTerm ),
	COALESCE(t2.IRD, t1.IRD ),
	COALESCE(t2.PreviousVehicle_ID, t1.PreviousVehicle_ID ),
	COALESCE(t2.PreviousVehicleMake, t1.PreviousVehicleMake ),
	COALESCE(t2.PreviousVehicleModel, t1.PreviousVehicleModel ),
	COALESCE(t2.PreviousVehicleRegNumber, t1.PreviousVehicleRegNumber ),
	COALESCE(t2.PreviousVehicleYearsOwned, t1.PreviousVehicleYearsOwned ),
	COALESCE(t2.FleetCode, t1.FleetCode ),
	COALESCE(t2.CompanyName, t1.CompanyName ),
	COALESCE(t2.C_address_id, t1.C_address_id ),
	COALESCE(t2.CompanyPhone, t1.CompanyPhone ),
	COALESCE(t2.FleetContactTitle, t1.FleetContactTitle ),
	COALESCE(t2.FleetContactFirstName, t1.FleetContactFirstName ),
	COALESCE(t2.FleetContactSurname, t1.FleetContactSurname ),
	COALESCE(t2.FleetContactJobTitle, t1.FleetContactJobTitle ),
	COALESCE(t2.OrderCreationDate, t1.OrderCreationDate ),
	COALESCE(t2.ExtractDate, t1.ExtractDate ),
	COALESCE(t2.REQUESTED_DELIVERY_DATE, t1.REQUESTED_DELIVERY_DATE ),
	COALESCE(t2.Customer_Offer_Code, t1.Customer_Offer_Code ),
	COALESCE(t2.delivery_status, t1.delivery_status ) ,
	T1.SEQ
FROM 
	CTE2 T1 
INNER JOIN cte t2 
	on	t1.OrderNumber = t2.OrderNumber 
	and	t1.Cust_ID = t2.Cust_ID 
	and t1.Vehicle_ID = t2.Vehicle_ID 
	and t1.SEQ = T2SEQ-1
)

select	* from CTE2
where SEQ = 1;

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-25 : 11:27:49

I've made some later changes to my last (before this one) post.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

epic monkey
Starting Member

4 Posts

Posted - 2011-11-29 : 06:05:52

Hi Nigel,
Thanks for your help again, Sorry for not getting back sooner I was out of office yesterday.
I kicked this query off on Friday on a copy of the table in a local instance (so that it would not impact prod) and selected all the results of CTE2 into a TempTable so that I could inspect the results on my return. Unfortunately, it ran for 3 days and 16 hours until I stopped it this morning. I'm thinking it's caused an infinite loop on the union with the "-1" but I cannot seem to isolate it.
Thanks for all the help so far.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-29 : 06:16:55

Don't run a first test on anything that you expect to take that long to run - you'll never complete in a reasonable timeframe.
Reduce the number of rows being accessed - I would start with one group by adding a where clause to the first cte.
That shold take seconds (if not you have an underlyikng design problem).

If you are running on a lot of data then you should probably optimise this (there are a few things that will be slow on large datasets) but depends on what you need.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

epic monkey
Starting Member

4 Posts

Posted - 2011-11-29 : 11:25:38

Thanks - I got it working. Appreciate all the help. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources