> As soon as you or somebody else can tell me how to merge Jayne's two
> rows into one,
> I'm sure I can write a plpgsql function to dynamically create the view
> you're looking for.
Ok, got it:
SELECT sid, sname, SUM("OPS") AS "OPS", SUM("MPD") AS "MPD" FROM (
SELECT staff.*, CASE dsdesc WHEN 'OPS' THEN rrank ELSE 0 END AS "OPS", CASE dsdesc WHEN 'MPD' THEN rrank ELSE
0END AS "MPD"
FROM staff,depts,ranks WHERE sid=rsid AND did=rdid ) as foo
GROUP BY sid, sname ;
sid | sname | OPS | MPD
-----+---------+-----+----- 1 | Rod | 0 | 3 2 | Jayne | 5 | 2 3 | Freddie | 3 | 0
(3 rows)
Gary,
I'm going to write the plpgsql function to dynamically amend the view.
In the meantime you may think about creating a trigger which fires every
time a new department is entered and which calls the function then.
Regards, Christoph