CODEWITHSUNDEEP
sqloracle-database
sukach
sukach

Reputation: 271

Combining data from one table in Oracle

This problem is difficult to describe, especially in the title. Perhaps it would be easier to show what I am trying to accomplish. This is an Oracle database.

Given the following DDL (http://sqlfiddle.com/#!4/e0730):

CREATE TABLE TEST_LIST(CONTRACT VARCHAR2(10), RECEIPT_CITY VARCHAR2(15), DELIVERY_CITY VARCHAR2(15));

INSERT INTO TEST_LIST VALUES ('CTR_01', 'DETROIT', '');
INSERT INTO TEST_LIST VALUES ('CTR_01', 'KALAMAZOO', '');
INSERT INTO TEST_LIST VALUES ('CTR_01', '', 'KALAMAZOO');
INSERT INTO TEST_LIST VALUES ('CTR_01', '', 'MUSKEGON');
INSERT INTO TEST_LIST VALUES ('CTR_01', 'SOUTH HAVEN', '');
INSERT INTO TEST_LIST VALUES ('CTR_02', 'BATTLE CREEK', '');
INSERT INTO TEST_LIST VALUES ('CTR_02', '', 'KALAMAZOO');

I am trying to get the following result:

CONTRACT  CITY          DIRECTION
--------  ------------  -----------
CTR_01    DETROIT       RECEIPT
CTR_01    KALAMAZOO     RECDEL
CTR_01    MUSKEGON      DELIVERY
CTR_01    SOUTH HAVEN   DELIVERY
CTR_02    BATTLE CREEK  RECEIPT
CTR_02    KALAMAZOO     DELIVERY

The part I'm struggling with is when the city is both a receipt and a delivery and getting it to show on one row as RECDEL. I've tried various combinations of UNIONs, INTERSECTs and FULL OUTER JOINs to no avail. Could LISTAGG be used? Not sure how that work work with the multiple columns. Any help would be appreciated.

Upvotes: 1

Views: 69

Answers (3)

triggerNZ
triggerNZ

Reputation: 4751

There is probably a cleaner way, but this works:

SELECT contract, receipt_city AS city, 'RECEIPT' as direction from test_list t1 where receipt_city IS NOT NULL AND NOT EXISTS (SELECT * FROM test_list t2 WHERE t2.contract = t1.contract and t2.delivery_city = t1.receipt_city )
UNION
SELECT contract, receipt_city AS city, 'RECDEL' as direction from test_list t1 where receipt_city IS NOT NULL AND EXISTS (SELECT * FROM test_list t2 WHERE t2.contract = t1.contract and t2.delivery_city = t1.receipt_city )
UNION
SELECT contract, delivery_city AS city, 'DELIVERY' as direction from test_list t1 where delivery_city IS NOT NULL AND NOT EXISTS (SELECT * FROM test_list t2 WHERE t2.contract = t1.contract and t2.receipt_city = t1.delivery_city )
ORDER BY contract;

Upvotes: 1

PM 77-1
PM 77-1

Reputation: 13334

Here's a solution with LISTAGG. It produces Delivery Receipt instead of RECDEL. I hope it's enough for you to get the idea.

SELECT contract,
       COALESCE(receipt_city, delivery_city, '') "City",
       LISTAGG(CASE WHEN receipt_city IS NOT NULL THEN 'Receipt' END ||
               CASE WHEN delivery_city IS NOT NULL THEN 'Delivery' END,
              ' ') 
                 WITHIN GROUP (ORDER BY contract) "Direction"
FROM test_list
GROUP BY contract, COALESCE(receipt_city, delivery_city, '');

SQL Fiddle

Upvotes: 1

Paul Maxwell
Paul Maxwell

Reputation: 35553

| CONTRACT |         CITY | DIRECTION |
|----------|--------------|-----------|
|   CTR_01 |      DETROIT |   RECEIPT |
|   CTR_01 |    KALAMAZOO |    RECDEL |
|   CTR_01 |     MUSKEGON |  DELIVERY |
|   CTR_01 |  SOUTH HAVEN |   RECEIPT |
|   CTR_02 | BATTLE CREEK |   RECEIPT |
|   CTR_02 |    KALAMAZOO |  DELIVERY |

by:

select distinct
      t1.contract
    , case when t1.delivery_city = t2.receipt_city then t1.delivery_city
           when t1.delivery_city is not null       then t1.delivery_city
           when t1.receipt_city is not null        then t1.receipt_city
           else 'unexpected'
      end as CITY
    , case when t1.delivery_city = t2.receipt_city 
             or t1.receipt_city = t3.delivery_city  then 'RECDEL'
           when t1.delivery_city is not null        then 'DELIVERY'
           when t1.receipt_city is not null         then 'RECEIPT'
           else 'unexpected'
      end as DIRECTION
from TEST_LIST t1
left join test_list t2
 on t1.CONTRACT = t2.CONTRACT
 and t1.delivery_city = t2.receipt_city
left join test_list t3
 on t1.CONTRACT = t3.CONTRACT
 and t1.receipt_city = t3.delivery_city
order by
      t1.contract
    , CITY
    , DIRECTION

see: http://sqlfiddle.com/#!4/57f0b/1

Upvotes: 2

Related Questions