INSERT new row consolidate similar rows

Question

Here is what I need to accomplish:

INSERT a new row into t_order by combining two or more rows from the same table (t_order) where all values are the same except for order_id (Identity) and order_number

The New Row will represent a consolidated order.

Two orders going to the same address get combined into one

Example Table before Insert

order_id   order_number   ship_addrs1    ship_to_zip
--------   ------------   -----------    -----------
   1       ABC001         111 1st St     11111
   2       ABC002         123 Main St    12345  <--- Source Row 
   3       ABC003         123 Main St    12345  <--- Source Row
   4       ABC004         111 2nd St     11111

Result After Insert (Source orders must remain)

order_id   order_number   ship_addrs1    ship_to_zip
--------   ------------   -----------    -----------
   1       ABC001         111 1st St     11111
   2       ABC002         123 Main St    12345
   3       ABC003         123 Main St    12345 
   4       ABC004         111 2nd St     11111
   5       ABC005         123 Main St    12345  <--- New Row

I have considered using the following code to accomplish this but not sure what I need to do to consolidate the three rows.

SELECT * INTO    tmp_order_cosolidation 
  FROM           t_order 
  WHERE          order_id = 1 AND order_number = ABC002

ALTER TABLE      tmp_order_cosolidation 
  DROP COLUMN    order_id, ordern_number

INSERT INTO      t_order 
  SELECT             * 
  FROM           tmp_order_cosolidation;

DROP TABLE       tmp_order_cosolidation ;

Thank you in advance for your answers

Answer 1

Your order table should have a few more columns to show whether the order is a consolidation, eligible for consolidation, or has already been consolidated. Here is my proposed solution, which has added columns. All inferred columns are in CAPS.

--VIEW ROWS TO INSERT
select count(order_id),ship_addrs1,ship_to_zip2
from t_order
where CONSOL_ELIGIBLE = 'Y' and CONSOL_COMPLETED = 'N'
group by ship_addrs1,ship_to_zip2
having count(order_id) > 1

--TEMP TABLE FOR INSERT
declare @tmptable TABLE (total_orders int,ship_addrs1 nvarchar(50),ship_to_zip2 nvarchar(50),CONSOL_ELIGIBLE nvarchar(1))
insert into @tmptable (total_orders, ship_addrs1,ship_to_zip2,CONSOL_ELIGIBLE)
(select count(order_id),ship_addrs1,ship_to_zip2,'N'
from t_order
where CONSOL_ELIGIBLE = 'Y' and CONSOL_COMPLETED = 'N'
group by ship_addrs1,ship_to_zip2
having count(order_id) > 1)

--INSERT FROM TEMP TABLE
insert into ORDER_TABLE (total_orders, ship_addrs1,ship_to_zip2,CONSOL_ELIGIBLE)
(select total_orders, ship_addrs1,ship_to_zip2,CONSOL_ELIGIBLE
from @tmptable)