CODEWITHSUNDEEP
sqldatabaseoracle-database
user2210179
user2210179

Reputation: 73

Oracle - Full Join a table against itself?

With a table supposedly like the following

 PK1             PKDate1       Field1
  A            4/14/2014      ABC
  A            4/15/2014      ABC
  B            4/14/2014      ABC
  B            4/15/2014      DEF
  C            4/15/2014      ABC
  D            4/14/2014      ABC

How can one write a query that takes in two dates, and compares Field1 for each of the values with date. The kicker is, if a row is existant on one date, but not the other, show it as null. IE the resultSet should be as follows (if passed in 4/14 + 4/15)

 PK1          Field1ForDay1     Field1ForDay2
  A                ABC             ABC
  B                ABC             DEF
  C               null             ABC
  D                ABC             null

The difficult part for me is getting the 'nulls' for one "sides" of the date when it exists for the other- where PK1 = 'C' doesnt exist with a 4/14 date to show up null AND vice versa (where PK1 = 'D', no record with a 4/15 date to show up null). Any possible help or headway on what should be able to help me out

Upvotes: 0

Views: 71

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270643

You can do this with group by and conditional aggregation:

select pk1,
       max(case when PKDate1 = to_date('2014-04-14', 'YYYY-MM-DD') then field1 end) as Field1ForDay1,
       max(case when PKDate1 = to_date('2014-04-15', 'YYYY-MM-DD')then field1 end) as Field1ForDay2
from table t
group by pk1;

Upvotes: 1

Related Questions