How to Merging Column

[Lotu]

Hi I have one table lets say "Vendor" with data :

PROJECT_ID CLIENT_NAME SP_NAME GC_NAME

1 Diligent & Co. NULL NULL
12 Diligent CLIENT. NULL NULL
11 NULL Tata_InfoTech NULL
12 NULL NULL TISCO
12 NULL Diligent_Cosco. NULL
11 Diligent_Delhi NULL NULL
16 NULL Diligent_INCL HCL Tech
16 NULL NULL WIPRO Tech

Now I want to create a view "SomeView" out of this which should return me only one row for each project_ID

so the output should be

PID CLIENT_ID SP_ID GC_ID

1 Diligent & Co. NULL NULL
11 Diligent_Delhi Tata_InfoTech NULL
12 Diligent_CLIENT Diligent_Cosco TISCO
16 NULL Diligent_INCL WIPRO_TECH


Assumption here is that for each project_ID there will be only one CLIENT, only one GC and only one SP



Can someone help me in achieving this

Regards
Lotu

[slimdave]

select PID,
max(CLIENT_NAME),
max(SP_ID),
max(GC_ID)
from ...
group by pid


by the way, that design really sucks.