Tricky Select: Display Col Value From One Row As Col On Every Row

In a system holding data from questionnaires, I have two defined tables: one holding info on the respondent (r) and the other holding answer data (ad)

The layout of the respondent table:

r_idint
weekint

The layout of the answer data table:

ad_id int
r_idint
qvarchar(10)
a_valint
a_text varchar(50)

Each row in the ad table matches data for one question on the questionnaire.

Lets say a

<sql>
select *
from respondent r, answer_data ad
where r.r_id = ad.r_id
</sql>

returns the following data:

<result>
r_id, week, ad_id, r_id, q, a_val, a_text
1, 40, 1, 1, '1', 1, 'Destination 1'
1, 40, 2, 1, '2', 1, 'Bad'
1, 40, 3, 1, '3', 3, 'Good'
2, 40, 4, 2, '1', 2, 'Destination 2'
2, 40, 5, 2, '2', 2, 'Acceptable'
2, 40, 6, 2, '3', 4, 'Excellent'
3, 41, 7, 3, '1', 1, 'Destination 1'
3, 41, 8, 3, '2', 4, 'Excellent'
3, 41, 9, 3, '3', 4, 'Excellent'
</result>

Extracting the mean value of answers by week is easily done using the following select:

<sql>
select week, q, avg(cast(a_val as float)) mean
from respondent r, answer_data ad
where r.r_id = ad.r_id
and q > '1'
group by q, week
order by q, week
</sql>

This would give a result like:

<result>
week, q, mean
40, '2', 1.5
40, '3', 4.0
41, '2', 3.5
41, '3', 4.0
</result>

Now the tricky part - a result by destination (ad.q = '1') has been requested by the customer.

Doing a

<sql>
select q, avg(cast(a_val as float)) mean
from respondent r, answer_data ad
where r.r_id = ad.r_id
and q > '1'
and r.r_id in (
select r.r_id
from respondent r, answer_data ad
where r.r_id = ad.r_id and q = '1'
)
group by q
order by q
</sql>

returns the requested data:

<result>
q, mean
'2', 2.3333333333333335
'3', 3.6666666666666665
</result>

Only, it lacks info on the destination. What I need is something like this:

<result>
dest, q, mean
'Destination 1', '2', 2.5
'Destination 1', '3', 3.5
'Destination 2', '2', 2.0
'Destination 2', '3', 4.0
</result>

How can I achieve that?

Thanks,
Jacob Dall