Solved

Help with SQL syntax to merge rows

Posted on 2011-09-10
3
259 Views
Last Modified: 2012-05-12
I have the following table which is populated by a user importing data into it:

 
CREATE TABLE [dbo].[tblPurchaseOrder_Detail](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[fkPurchaseOrder] [int] NULL,
	[QuantityOrdered] [int] NULL,
	[QuantityShipped] [int] NULL,
	[ProductNumber] [varchar](32) NULL,
	[ProductDescription] [varchar](4096) NULL,
	[UnitPrice] [money] NULL,
	[DirectShip] [bit] NULL,
	[DistributorOrderNumber] [varchar](16) NULL,
	[CiscoOrderNumber] [varchar](16) NULL,
	[EstShipDate] [date] NULL,
	[ActualShipDate] [date] NULL,
	[CiscoDART] [varchar](32) NULL,
 CONSTRAINT [PK_tblPurchaseOrder_Detail] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Open in new window


When a user uploads data there may be the same item contained on multiple lines of the PO.  For example the PO might have:
qty 5 of SKU ABC
qty 1 of SKU XYZ
qty 7 of SKU DEF
qty 4 of SKU ABC

When the data is imported I need to "merge" the duplicate SKUs and SUM the quantity.  In the preceding data set that would mean that the end result looks like:

qty 9 of SKU ABC
qty 1 of SKU XYZ
qty 7 of SKU DEF

Is there a way to do this with SQL syntax (im assuming that there is), or do I need to do this programatically?  Ideally I would like a SQL querty that I can run after the batch upload process to merge the rows.  Anyone have any ideas?

The selection criteria for the query would be fkPurchaseOrder and ProductNumber.
0
Comment
Question by:DanLockwood
  • 2
3 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36517653
When you say "import" please clarify if this is via some automated process or if we are talking an INSERT INTO statement. For the former, you may have to import to a staging table and then you can use:

INSERT INTO target_table({column list})
SELECT SKU, {other grouping columns}, SUM([QuantityOrdered])
FROM staging_table
GROUP BY SKU, {other grouping columns}

Note though from a business perspective, you may have multiple lines on a purchase/sales order because the required ship date is different. Therefore, you may not be able to do this aggregate without compromising/losing some of the detail associated. This only "works" if the lines are essentially the same details except quantity. I know that often happens with cross-selling of consumables and the way business users think is they enter part x, then add a quantity of xy consumable to the order, then they add part y, then another quantity of xy consumable. The separation denotes the different needs, but since xy is the same exact part then from inventory perspective it is just one large order of xy indifferent to which part it is a consumable for at the ultimate destination. As another note, this too may be important to the customer. They may have the PO laid out a certain way so when the inventory is received they can route the parts appropriately, i.e., the line numbers and specific quantities are allocated and they use the ship list to organize the receipt.

I guess my advice is ensure that this is "really" what you want to do.

Anyway, moving on...if you have lines that may already be in the database, then you will can look at MERGE or UPDATE syntax. What you will want is that on an UPDATE, you:
SET table_to.[QuantityOrdered] = table_to.[QuantityOrdered] + table_from.[QuantityOrdered]

In other words, you have to increment and not replace.

Hope that helps!
0
 

Author Comment

by:DanLockwood
ID: 36522907
Thank you for this reply.  It is very insightful.  Unfortunately yes I do really want to conduct the merge.  This is a function of our suppliers also having "merged" products on our POs.  If I use the UPDATE approach that you describe, how would you suggest that I then DELETE the rows which are consolidated?  I've never used MERGE before and although it looks like it might be the right tool for the job, i'm leery.  Can you provide an example that would apply to this situation?  Thanks.
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 36523811
Here is an example of how it might work:
create table #merge_test(
   orderguid uniqueidentifier not null default(newid()),
   itemid varchar(100),
   orderqty decimal(14,4),
   primary key(orderguid)
);

INSERT INTO #merge_test(ItemID, OrderQty)
VALUES('112233', 20);

/* http://technet.microsoft.com/en-us/library/bb510625.aspx */
MERGE #merge_test AS tto
    USING (
	   SELECT POItem, SUM(POQty)
	   FROM (
	      VALUES('112233', 100),
	            ('112234', 200),
			    ('112233', 50),
			    ('112233', 50),
			    ('112235', 111)
		) staging(POItem, POQty)
		GROUP BY POItem
	) AS tfr (POItem, POQty)
    ON (tto.ItemID = tfr.POItem)
    WHEN MATCHED THEN 
        UPDATE SET orderqty = orderqty + tfr.POQty
	WHEN NOT MATCHED THEN	
	    INSERT (ItemID, OrderQty)
	    VALUES (tfr.POItem, tfr.POQty)
;

SELECT OrderGUID, ItemID, OrderQty
FROM #merge_test
;

drop table #merge_test;

Open in new window


Note: the aggregation of the source data is intentional. You will want to ensure that you are grouping any new data coming in so that it unique within the MERGE. But as you will see in the sample, it will handle matching to existing rows in the data and updating just fine. The results of the query is just three rows with '112233' having 220 for order quantity.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now