How to design a tough query...

[Greek_Man]

I have problem to design a query.
I'm not an expert in SQL but I have a solid base.
I need to make a report and I try to define the query that will give me information that I want.

I have the 2 following tables:

TABLE STATUS.

CREATE TABLE STATUS
(
VID NUMBER(3),
SID NUMBER(3),
PID NUMBER(3),
STATUS_VAL NUMBER(3)
)

and
TABLE CENTRE.

CREATE TABLE CENTRE
(
SID VARCHAR2(3 BYTE),
NAME VARCHAR2(100 BYTE),
)

and I want the following report:

SID - NAME - PID - V1 - V2 - V3
100 Joe 2 Clean Clean Clean
101 Bill 1 Clean Clean Not Clean
101 Bill 2 Clean Not Clean Clean

where v1 = CLEAN if vid = 1 and status = 2
where v2 = CLEAN if vid = 2 and status = 2
where v1 = CLEAN if vid = 3 and status = 2

Is it possible to do this with SQL (with the existant structure of the tables) or it's impossible ?

I search a lot (the nearest thing that I found is a subquery in the FROM clause but it's still not the correct solution).

Any advice are appreciated,
(And, by the way, I can't change the structure of the tables so even if it's not a good design, I need to deal with it).

Thanks to all,

Greek_Man.

[pando]

use case()

[Greek_Man]

Thanks Pando,

I'm currently trying to resolve my problem with your advice and I think I will be soon able to reach my goal with this solution.

Thanks again,

Greek_Man