Solved

Help with SQL syntax to merge rows

Posted on 2011-09-10
3
268 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Sub-Query Help 22 62
Select only the top record in a left join 13 35
export sql results to csv 6 35
Mysql Left Join Case 10 54
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

772 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