CODEWITHSUNDEEP
sqloracle-databaseoracle10g
user892234
user892234

Reputation: 23

How can I write a query to combine data?

I have two queries.

The first query looks like this :

SELECT subject_id, period, marks FROM subject_details;

This returns the following data set:

subject_id         period       marks
----------         ------       -----
1                   Jan          50
1                   Feb          40
1                   Mar          30
2                   Jan          60

The second query follows :

SELECT subject.subject_id, periods.month, subject.marks  FROM 
(SELECT subject_id, period, marks FROM subject_details) subject,
(WITH PERIOD_COUNTER AS (
SELECT LEVEL-1 AS ID 
FROM DUAL 
CONNECT BY LEVEL <= 6
) 
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('01/01/2011', 'DD/MM/RRRR'), ID),'Mon') 
month FROM PERIOD_COUNTER) periods 
WHERE subject.period (+) = periods.month;


subject_id         period       marks
----------         ------       -----
1                   Jan          50
1                   Feb          40
1                   Mar          30
2                   Jan          60
null                Apr          null
null                May          null
null                Jun          null

How can I write a query that would give me the following result set (The intention is to basically create a result set that fills in the data for missing months for every subject_id.):

subject_id         period       marks
----------         ------       -----
1                   Jan          50
1                   Feb          40
1                   Mar          30
1                   Apr          null
1                   May          null
1                   Jun          null
2                   Jan          60
2                   Feb          null
2                   Mar          null
2                   Apr          null
2                   May          null
2                   Jun          null

Upvotes: 2

Views: 112

Answers (3)

Lucio M. Tato
Lucio M. Tato

Reputation: 5835

Why don't you do a cartesian join?

Let's assume you also have a table "subjets" and a table "periods" (with 6 records)


SELECT a.subject_id, b.period
  , (select marks FROM subject_details c 
     where c.subject_id=a.subject_id and c.period = b.period
     ) as marks 
from subjects a, periods b

Didn't test it.

Upvotes: 0

Jon Heller
Jon Heller

Reputation: 36922

A partitioned outer join allows you to join the 6 months to every subject_id:

with subject_details as
(
    select 1 subject_id, 'Jan' period, 50 marks from dual union all
    select 1 subject_id, 'Feb' period, 40 marks from dual union all
    select 1 subject_id, 'Mar' period, 30 marks from dual union all
    select 2 subject_id, 'Jan' period, 60 marks from dual
)
select subject_details.subject_id, months.period, subject_details.marks
from
(
    select to_char(add_months(date '2011-01-01', level-1), 'Mon') period
    from dual connect by level <= 6
) months
left outer join subject_details
    partition by (subject_id)
    on months.period = subject_details.period

Upvotes: 1

user610217
user610217

Reputation:

this is a UNION call

SELECT subject_id, period, marks FROM subject_details

UNION

SELECT subject.subject_id, periods.month, subject.marks  FROM 
(SELECT subject_id, period, marks FROM subject_details) subject,
(WITH PERIOD_COUNTER AS (
SELECT LEVEL-1 AS ID 
FROM DUAL 
CONNECT BY LEVEL <= 6
) 
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('01/01/2011', 'DD/MM/RRRR'), ID),'Mon') 
month FROM PERIOD_COUNTER) periods 
WHERE subject.period (+) = periods.month;

Upvotes: 0

Related Questions