sql - How do I link different values in a column according to one value of another field? -

sorry misspecification of question , problem not easy explained in few words, given not expert in sql.

i have dataset containing 3 field:

id   sector      grade  1      aaa       junior 2      aaa       senior 3      aaa       director1 4      bbb       junior 5      bbb       senior 6      bbb       director2 

id refers different persons, , grade, grade within organisation.

i trying have following output adding column describing director each id. i.e sector 'aaa' have 'director1' , sector 'bbb' have 'director2'. therefore have:

id   sector       grade       director   1      aaa       junior        director1 2      aaa       senior        director1 3      aaa       director1     director1 4      bbb       junior        director2 5      bbb       senior        director2 6      bbb       director2     director2 

i think cursor sure there simple way so.

the structure is:

select r.id, d.sector, d.grade resource left join d.dimension on d.id=r.id 

you can construct temporary table of directors with:

select d.sector sector, d.grade director d.dimension d.grade 'director%' 

and can join original query "sector director" table:

select r.id, d.sector, d.grade, dir.director resource left join d.dimension on d.id=r.id join (     select d.sector sector, d.grade director     d.dimension     d.grade 'director%' ) dir on dir.sector = d.sector