I've a table with these values:
OBJ_ID |DAT | PROP_ID | VALUE
------------------------------------------------
1 | 06-JUL-2011 | 1 | 100
1 | 06-JUL-2011 | 2 | 90
1 | 14-JUL-2011 | 1 | 150
1 | 14-JUL-2011 | 2 | 90
1 | 14-JUL-2011 | 3 | 50
Now, I want to have this result:
ID | VAL
----------------------
| 06-JUL-2011, 14-JUL-2011
1 | 100, 90
2 | 150, 90
3 | 0, 50
This result I want to write to a CSV file, which my user can download.
I've created this query:
select null id, wm_concat(distinct DAT) val
from my_table
where OBJ_ID = :P_OBJ_ID
union
select PROP_ID ID, wm_concat(nvl(VALUE,0)) val
from my_table
where OBJ_ID = :P_OBJ_ID
This query gives the following result:
ID | VAL
----------------------
| 06-JUL-2011, 14-JUL-2011
1 | 100, 90
2 | 150, 90
3 | 50
What I want, is to see a '0' when the property does not excist on a date, when it does excist, I want to see the value of this property.
How do I have to do that?
I'm using a 11g database.