Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Help with SQL syntax to merge rows

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

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 60

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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

916 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