SQL & PL/SQL

Dear,

I would like to have a little help from you. My goal is to create a view where data must be in appropriate format. In this format i would like to have for each group one header.

I have 3 tables which they ar connected to each other (representing hierachical data where financial plan breaks across different groups).

CRM_PLAN
CRM_PLAN_ID
DATE
AMOUNT

CRM_PLAN_PE
CRM_PLAN_PE_ID
TITLE
AMOUNT
CRM_PLAN_ID (ref key to CRM_PLAN.CRM_PLAN_ID)

CRM_PLAN_MONTH
CRM_PLAN_MONTH_ID
YEAR
MONTH
AMOUNT
CRM_PLAN_PE_ID (foreign key to CRM_PLAN_PE.CRM_PLAN_PE_ID)

Data looks like:

CRM_PLAN
CRM_PLAN_ID | DATE | AMOUNT
1 | 01.01.2012 | 500
2 | 01.02.2012 | 200

CRM_PLAN_PE
CRM_PLAN_PE_ID |TITLE | AMOUNT | CRM_PLAN_ID
1 | Organization 1 |100 | 1
2 | Organization 2 |400 | 1
3 | Organization 3 |200 | 2


CRM_PLAN_MONTH
CRM_PLAN_MONTH_ID | YEAR | MONTH | AMOUNT | CRM_PLAN_PE_ID
1 | 2012| 1 | 60 |1
2 | 2012| 2 | 40 |1
3 | 2012| 1 | 350 |2
4 | 2012| 2 | 50 |2
5 | 2012| 1 | 200 |3


I would like view which would return data like:

MONTH | AMOUNT
Organization 1 |null
1 | 60
2 | 40
Organization 2 | null
1 | 350
2 | 50
Organization 3 | null
1 | 200

The main question is hot to write an sql as source for view to insert before each group a row with title column value from table CRM_PLAN_PE. Is that even possible? Maybe you could suggest me some other method.

View will be used in oracle form where will be possible to edit data - I will arrange that through insted of trigger.

Regards,
Igor

Edited by: user5528050 on 29.11.2012 2:23

Edited by: user5528050 on 29.11.2012 2:24

Edited by: user5528050 on 29.11.2012 2:25