?
Solved

Help with SQL syntax to merge rows

Posted on 2011-09-10
3
Medium Priority
?
308 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 61

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 61

Accepted Solution

by:
Kevin Cross earned 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

621 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