Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Help with SQL syntax to merge rows

Posted on 2011-09-10
3
Medium Priority
?
301 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

722 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