CODEWITHSUNDEEP
sqloracle-databasesybase
Icebreaker
Icebreaker

Reputation: 297

Pulling data while pivoting at the same time

   ID | Type     | Code
   1    Purchase   A1
   1    Return     B1
   1    Exchange   C1
   2    Purchase   D1
   2    Return     NULL
   2    Exchange   F1
   3    Purchase   G1
   3    Return     H1
   3    Exchange   I1
   4    Purchase   J1
   4    Exchange   K1

Above is sample data. What I want to return is:

ID | Type     | Code
 1    Purchase   A1
 1    Return     B1
 1    Exchange   C1
 3    Purchase   G1
 3    Return     H1
 3    Exchange   I1

So if a field is null in code or the values of Purchase, Return and Exchange are not all present for that ID, ignore that ID completely. However there is one last step. I want this data to then be pivoted this way:

ID | Purchase | Return | Exchange
1    A1         B1       C1
3    G1         H1       I1

I asked this yesterday without the pivot portion which you can see here:

SQL query to return data only if ALL necessary columns are present and not NULL

However I forgot to note the last part. I tried to play around with excel but had no luck. I tried to make a temp table but the data is too large to do that so I was wondering if this could all be done in 1 sql statement?

I personally used this query with success:

select t.*
from t
where 3 = (select count(distinct t2.type)
       from t t2
       where t2.id = t.id and
             t2.type in ('Purchase', 'Exchange', 'Return') and
             t2.Code is not null
      );

So how can we adjust that to include the pivot part. Is that possible?

Upvotes: 0

Views: 45

Answers (3)

Mark Adelsberger
Mark Adelsberger

Reputation: 45789

UPDATE - Based on discussion in the question comments, my previous query had a faulty assumption (which I carried over from what I thought I saw in the original query in the question); I've eliminated the bad assumption.

select id
     , max(case when type='Purchase' then Code end) Purchase
     , max(case when type='Return' then Code end) Return
     , max(case when type='Exchange' then Code end) Exchange
  from t
 where code is not null
   and type in ('Purchase', 'Return', 'Exchange')
 group by id
having count(distinct type) = 3

Upvotes: 1

user5683823
user5683823

Reputation:

I will point out again (as I did in your other thread) that analytic functions will do the job much faster - they need the base table to be read just once, and there are no explicit or implicit joins. 

   with
         test_data ( id, type, code ) as (
           select 1, 'Purchase', 'A1' from dual union all
           select 1, 'Return'  , 'B1' from dual union all
           select 1, 'Exchange', 'C1' from dual union all
           select 2, 'Purchase', 'D1' from dual union all
           select 2, 'Return'  , null from dual union all
           select 2, 'Exchange', 'F1' from dual union all
           select 3, 'Purchase', 'G1' from dual union all
           select 3, 'Return'  , 'H1' from dual union all
           select 3, 'Exchange', 'I1' from dual union all
           select 4, 'Purchase', 'J1' from dual union all
           select 4, 'Exchange', 'K1' from dual
         )
    -- end of test data; actual solution (SQL query) begins below this line
select id, purchase, return, exchange
from   ( select id, type, code
         from   ( select id, type, code,
                    count( distinct case when type in ('Purchase', 'Return', 'Exchange')
                                         then type end                         
                         ) over (partition by id)             as ct_type,
                    count( case when code is null then 1 end
                         ) over (partition by id)             as ct_code
                  from   test_data
                )
         where  ct_type = 3 and ct_code = 0
       )
pivot  ( min(code) for type in ('Purchase' as purchase, 'Return'   as return, 
                                                        'Exchange' as exchange)
       )
;

Output:

 ID PURCHASE RETURN   EXCHANGE
--- -------- -------- --------
  1 A1       B1       C1
  3 G1       H1       I1

2 rows selected.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270663

Quite easily. Just use conditional aggregation:

select t.id,
       max(case when type = 'Purchase' then code end) as Purchase,
       max(case when type = 'Exchange' then code end) as Exchange,
       max(case when type = 'Return' then code end) as Return
from t
where 3 = (select count(distinct t2.type)
       from t t2
       where t2.id = t.id and
             t2.type in ('Purchase', 'Exchange', 'Return') and
             t2.Code is not null
      )
group by t.id;

This is actually simpler to express (in my opinion) using having without the subquery:

select t.id,
       max(case when type = 'Purchase' then code end) as Purchase,
       max(case when type = 'Exchange' then code end) as Exchange,
       max(case when type = 'Return' then code end) as Return
from t
group by t.id
having max(case when type = 'Purchase' then code end) is not null and
       max(case when type = 'Exchange' then code end) is not null and
       max(case when type = 'Return' then code end) is not null;

Many databases would allow:

having Purchase is not null and Exchange is not null and Return is not null

But Oracle doesn't allow the use of table aliases in the having clause.

Upvotes: 2

Related Questions