Sql - add row when not existed
- by Nguyen Tuan Linh
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)
