1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
|
Select pivt.resource_id,
ahs.first_name,
ahs.surname,
ser.service_id,
ser.serviceoff_id,
ser.offering_desc,
sum(case pivt.rn when 1 then pivt.response end) / (Select count(*) From eval_dtresponse dtr Where dtr.rn =1 and dtr.serviceoff_fk=pivt.serviceoff_fk) AS reponse1,
sum(case pivt.rn when 2 then pivt.response end) / (Select count(*) From eval_dtresponse dtr Where dtr.rn =2 and dtr.serviceoff_fk=pivt.serviceoff_fk) AS reponse2,
sum(case pivt.rn when 3 then pivt.response end) / (Select count(*) From eval_dtresponse dtr Where dtr.rn =3 and dtr.serviceoff_fk=pivt.serviceoff_fk) AS reponse3,
sum(case pivt.rn when 4 then pivt.response end) / (Select count(*) From eval_dtresponse dtr Where dtr.rn =4 and dtr.serviceoff_fk=pivt.serviceoff_fk) AS reponse4,
sum(case pivt.rn when 5 then pivt.response end) / (Select count(*) From eval_dtresponse dtr Where dtr.rn =5 and dtr.serviceoff_fk=pivt.serviceoff_fk) AS reponse5,
sum(case pivt.rn when 6 then pivt.response end) / (Select count(*) From eval_dtresponse dtr Where dtr.rn =6 and dtr.serviceoff_fk=pivt.serviceoff_fk) AS reponse6
From eval_dtresponse pivt
INNER JOIN aemserviceoff ser on (ser.serviceoff_pk = pivt.serviceoff_fk and ser.client='P1')
INNER JOIN ahsresources ahs on ( ahs.resource_id = pivt.resource_id)
where 1=1 and pivt.type_id='ECHELLE'
Group by
pivt.resource_id,
ahs.first_name,
ahs.surname,
pivt.serviceoff_fk,
ser.service_id,
ser.serviceoff_id,
ser.offering_desc
--pivt.rn,
--pivt.is_numeric_scale
Order by ser.service_id, ser.serviceoff_id,pivt.resource_id |
Partager