Suppose I have a query that returns result like this:

Project Year Type Amt
PJ00001 2012 1    1000
PJ00001 2012 2    1000
PJ00001 2011 1    1000
PJ00002 2012 1    1000

What I want: Every Project will have 2 rows of Types for each Year. If the row is not there, add it to the result with Amt = 0.
For example:
- PJ00001 have 2 rows of type 1,2 in 2012 --> OK. But in 2011, it only have 1 row of Type 1 --> We add one row:PJ00001 2011 2 0
- PJ00002 have only 1 row of type 1 --> add:PJ00002 2012 2 0

Is there a way to easily do it. The only way I know now is to create a view like: PJ_VIEW. And then:

SELECT *
FROM PJ_VIEW
UNION ALL
SELECT t.PROJECT, t.YEAR_NO, 1 AS TYPE_NO, 0 AS AMT
FROM PJ_VIEW t
WHERE NOT EXISTS (SELECT 1 FROM PJ_VIEW t2 WHERE t2.PROJECT = t.PROJECT AND t2.YEAR_NO = t.YEAR_NO AND t2.TYPE_NO = 1)
UNION ALL
SELECT t.PROJECT, t.YEAR_NO, 2 AS TYPE_NO, 0 AS AMT
FROM PJ_VIEW t
WHERE NOT EXISTS (SELECT 1 FROM PJ_VIEW t2 WHERE t2.PROJECT = t.PROJECT AND t2.YEAR_NO = t.YEAR_NO AND t2.TYPE_NO = 2)