WITH dott AS ( SELECT ROW_NUMBER() OVER(PARTITION BY aa_id, stu_id ORDER BY aa_id, stu_id, quesito_id ) AS numdom, user_comp_id, questionario_id, quest_comp_id, aa_id, stu_id, elem_cod, risposte FROM ( SELECT v_tag.aa_id, v_tag1.stu_id, v.questionario_id, v.quesito_id, v.elem_cod, v_tag1.user_comp_id, v_tag.quest_comp_id, --listagg(DISTINCT nvl(TRIM(v.testo_libero), v.elem_des), ' ** ') within --listagg( case when v.risposta_id IS NOT NULL then nvl(TRIM(v.testo_libero), v.elem_des) else 'NULL' end, ' ** ') within --listagg( case when v.risposta_id IS NOT NULL then v.elem_cod else 'NULL' end, ' ** ') within LISTAGG( CASE WHEN v.risposta_id IS NOT NULL THEN nvl(TRIM(v.testo_libero), v.elem_des) ELSE CASE WHEN x.quesito_id IS NULL THEN 'NP' ELSE '' END END, ' ** ') WITHIN GROUP( ORDER BY v.quesito_id ) AS risposte FROM v02_gen_questionario_ext v LEFT JOIN p02_quesiti_visti x ON x.quesito_id = v.quesito_id AND x.quest_comp_id = v.quest_comp_id INNER JOIN v02_quest_post_login_tag_comp v_tag ON v.questionario_id = v_tag.questionario_id AND v.quest_comp_id = v_tag.quest_comp_id INNER JOIN v02_quest_post_login_tag_user v_tag1 ON ( v_tag1.dataora = v_tag.dataora ) WHERE 1 = 1 AND v.tipo_elem_cod = 'Q_RISPOSTA' --AND v.risposta_id IS NOT NULL AND ( ( v.risposta_id IS NOT NULL -- casi di risposta data per domande NON multiple AND NOT EXISTS ( SELECT 1 FROM v02_gen_questionario_ext x WHERE x.questionario_id = v.questionario_id AND x.quest_comp_id = v.quest_comp_id AND x.quesito_id = v.parent_quesito_id AND x.tipo_elem_cod LIKE '%DOMAN%' AND x.tipo_formato_cod LIKE 'TL_DOM_%FM' ) ) OR -- casi di domande multiple: mostro una colonna per ogni risposta possibile EXISTS ( SELECT 1 FROM v02_gen_questionario_ext x WHERE x.questionario_id = v.questionario_id AND x.quest_comp_id = v.quest_comp_id AND x.quesito_id = v.parent_quesito_id AND x.tipo_elem_cod LIKE '%DOMAN%' AND x.tipo_formato_cod LIKE 'TL_DOM_%FM' ) OR -- casi di quesiti non mostrati all'utente per domande NON multiple (perchè trattate nello step prima): prevedo una sola colonna ( x.quesito_id IS NULL AND NOT EXISTS ( SELECT 1 FROM v02_gen_questionario_ext x WHERE x.questionario_id = v.questionario_id AND x.quest_comp_id = v.quest_comp_id AND x.quesito_id = v.parent_quesito_id AND x.tipo_elem_cod LIKE '%DOMAN%' AND x.tipo_formato_cod LIKE 'TL_DOM_%FM' ) AND v.quesito_id = ( SELECT MIN(x.quesito_id) FROM v02_gen_questionario_ext x WHERE x.questionario_id = v.questionario_id AND x.quest_comp_id = v.quest_comp_id AND x.parent_quesito_id = v.parent_quesito_id ) ) ) GROUP BY v_tag.aa_id, v_tag1.stu_id, v.quesito_id, v.elem_cod, v.questionario_id, v_tag.quest_comp_id, v_tag1.user_comp_id, v.ord_vis ORDER BY v.ord_vis ASC ) ) SELECT DISTINCT v2.quest_comp_id id_compilazione, (select ds_ateneo_des from p06_atenei_des_lin where ateneo_id in (select val_num from par_conf where par_cod='ATE') and lingua_id=5) Ateneo_Des, v_tagl.cds_des cds_des, v_tagl.aa_id aa_id_comp, ia.anno_corso anno_corso_iscr, ia.aa_iscr_id AS aa_iscr_riferimento, v_tagl.dataora AS data_ora, ris1.risposte AS d_a_d1, ris2.risposte AS d_a_d2, ris3.risposte AS d_a_d3_a1, ris4.risposte AS d_a_d3_a2, ris5.risposte AS d_a_d3_a3, ris6.risposte AS d_a_d4_a1, ris7.risposte AS d_a_d4_a2, ris8.risposte AS d_a_d4_a3, ris9.risposte AS d_a_d4_a4, ris10.risposte AS d_a_d5_a1, ris11.risposte AS d_a_d5_a2, ris12.risposte AS d_a_d5_a3, ris13.risposte AS d_a_d6, ris14.risposte AS d_a_d7, ris15.risposte AS d_a_d8, ris16.risposte AS d_a_d9_a1, ris17.risposte AS d_a_d9_a2, ris18.risposte AS d_a_d9_a3, ris19.risposte AS d_a_d9_a4, ris20.risposte AS d_a_d9_a5, ris21.risposte AS d_a_d9_a6, ris22.risposte AS d_a_d9_a7, ris23.risposte AS d_a_d10, ris24.risposte AS d_a_d11, ris25.risposte AS d_a_d12_a1, ris26.risposte AS d_a_d12_a2, ris27.risposte AS d_a_d12_a3, ris28.risposte AS d_a_d12_a4, ris29.risposte AS d_a_d12_a5, ris30.risposte AS d_a_d12_a6, ris31.risposte AS d_a_d12_a7, ris32.risposte AS d_a_d13, ris33.risposte AS d_a_d14, ris34.risposte AS d_a_d15, ris35.risposte AS d_a_d16, ris36.risposte AS d_b_d1, ris37.risposte AS d_b_d2, ris38.risposte AS d_b_d3, ris39.risposte AS d_b_d4, ris40.risposte AS d_b_d5, ris41.risposte AS d_b_d6, ris42.risposte AS d_b_d7, ris43.risposte AS d_b_d8, ris44.risposte AS d_b_d9, ris45.risposte AS d_b_d10, ris46.risposte AS d_b_d11, ris47.risposte AS d_b_d12, ris48.risposte AS d_b_d13, ris49.risposte AS d_b_d14, ris50.risposte AS d_b_d15, ris51.risposte AS d_b_d16, ris52.risposte AS d_b_d17, ris53.risposte AS d_b_d18, ris54.risposte AS d_b_d19, ris55.risposte AS d_b_d20, ris56.risposte AS d_b_d21, ris57.risposte AS d_b_d22, ris58.risposte AS d_b_d23, ris59.risposte AS d_b_d24, ris60.risposte AS d_b_d25, ris61.risposte AS d_b_d26, ris62.risposte AS test FROM v02_gen_questionario v2 INNER JOIN v02_quest_post_login_tag_comp v_tagl ON ( v_tagl.quest_comp_id = v2.quest_comp_id ) LEFT JOIN dott ris1 ON v2.quest_comp_id = ris1.quest_comp_id AND ris1.numdom = 1 INNER JOIN p04_iscr_ann ia ON ( ia.stu_id = ris1.stu_id AND ia.aa_iscr_id=v_tagl.aa_id-1 AND v_tagl.aa_id>ia.aa_iscr_id) LEFT JOIN dott ris2 ON v2.quest_comp_id = ris2.quest_comp_id AND ris2.numdom = 2 LEFT JOIN dott ris3 ON v2.quest_comp_id = ris3.quest_comp_id AND ris3.numdom = 3 LEFT JOIN dott ris4 ON v2.quest_comp_id = ris4.quest_comp_id AND ris4.numdom = 4 LEFT JOIN dott ris5 ON v2.quest_comp_id = ris5.quest_comp_id AND ris5.numdom = 5 LEFT JOIN dott ris6 ON v2.quest_comp_id = ris6.quest_comp_id AND ris6.numdom = 6 LEFT JOIN dott ris7 ON v2.quest_comp_id = ris7.quest_comp_id AND ris7.numdom = 7 LEFT JOIN dott ris8 ON v2.quest_comp_id = ris8.quest_comp_id AND ris8.numdom = 8 LEFT JOIN dott ris9 ON v2.quest_comp_id = ris9.quest_comp_id AND ris9.numdom = 9 LEFT JOIN dott ris10 ON v2.quest_comp_id = ris10.quest_comp_id AND ris10.numdom = 10 LEFT JOIN dott ris11 ON v2.quest_comp_id = ris11.quest_comp_id AND ris11.numdom = 11 LEFT JOIN dott ris12 ON v2.quest_comp_id = ris12.quest_comp_id AND ris12.numdom = 12 LEFT JOIN dott ris13 ON v2.quest_comp_id = ris13.quest_comp_id AND ris13.numdom = 13 LEFT JOIN dott ris14 ON v2.quest_comp_id = ris14.quest_comp_id AND ris14.numdom = 14 LEFT JOIN dott ris15 ON v2.quest_comp_id = ris15.quest_comp_id AND ris15.numdom = 15 LEFT JOIN dott ris16 ON v2.quest_comp_id = ris16.quest_comp_id AND ris16.numdom = 16 LEFT JOIN dott ris17 ON v2.quest_comp_id = ris17.quest_comp_id AND ris17.numdom = 17 LEFT JOIN dott ris18 ON v2.quest_comp_id = ris18.quest_comp_id AND ris18.numdom = 18 LEFT JOIN dott ris19 ON v2.quest_comp_id = ris19.quest_comp_id AND ris19.numdom = 19 LEFT JOIN dott ris20 ON v2.quest_comp_id = ris20.quest_comp_id AND ris20.numdom = 20 LEFT JOIN dott ris21 ON v2.quest_comp_id = ris21.quest_comp_id AND ris21.numdom = 21 LEFT JOIN dott ris22 ON v2.quest_comp_id = ris22.quest_comp_id AND ris22.numdom = 22 LEFT JOIN dott ris23 ON v2.quest_comp_id = ris23.quest_comp_id AND ris23.numdom = 23 LEFT JOIN dott ris24 ON v2.quest_comp_id = ris24.quest_comp_id AND ris24.numdom = 24 LEFT JOIN dott ris25 ON v2.quest_comp_id = ris25.quest_comp_id AND ris25.numdom = 25 LEFT JOIN dott ris26 ON v2.quest_comp_id = ris26.quest_comp_id AND ris26.numdom = 26 LEFT JOIN dott ris27 ON v2.quest_comp_id = ris27.quest_comp_id AND ris27.numdom = 27 LEFT JOIN dott ris28 ON v2.quest_comp_id = ris28.quest_comp_id AND ris28.numdom = 28 LEFT JOIN dott ris29 ON v2.quest_comp_id = ris29.quest_comp_id AND ris29.numdom = 29 LEFT JOIN dott ris30 ON v2.quest_comp_id = ris30.quest_comp_id AND ris30.numdom = 30 LEFT JOIN dott ris31 ON v2.quest_comp_id = ris31.quest_comp_id AND ris31.numdom = 31 LEFT JOIN dott ris32 ON v2.quest_comp_id = ris32.quest_comp_id AND ris32.numdom = 32 LEFT JOIN dott ris33 ON v2.quest_comp_id = ris33.quest_comp_id AND ris33.numdom = 33 LEFT JOIN dott ris34 ON v2.quest_comp_id = ris34.quest_comp_id AND ris34.numdom = 34 LEFT JOIN dott ris35 ON v2.quest_comp_id = ris35.quest_comp_id AND ris35.numdom = 35 LEFT JOIN dott ris36 ON v2.quest_comp_id = ris36.quest_comp_id AND ris36.numdom = 36 LEFT JOIN dott ris37 ON v2.quest_comp_id = ris37.quest_comp_id AND ris37.numdom = 37 LEFT JOIN dott ris38 ON v2.quest_comp_id = ris38.quest_comp_id AND ris38.numdom = 38 LEFT JOIN dott ris39 ON v2.quest_comp_id = ris39.quest_comp_id AND ris39.numdom = 39 LEFT JOIN dott ris40 ON v2.quest_comp_id = ris40.quest_comp_id AND ris40.numdom = 40 LEFT JOIN dott ris41 ON v2.quest_comp_id = ris41.quest_comp_id AND ris41.numdom = 41 LEFT JOIN dott ris42 ON v2.quest_comp_id = ris42.quest_comp_id AND ris42.numdom = 42 LEFT JOIN dott ris43 ON v2.quest_comp_id = ris43.quest_comp_id AND ris43.numdom = 43 LEFT JOIN dott ris44 ON v2.quest_comp_id = ris44.quest_comp_id AND ris44.numdom = 44 LEFT JOIN dott ris45 ON v2.quest_comp_id = ris45.quest_comp_id AND ris45.numdom = 45 LEFT JOIN dott ris46 ON v2.quest_comp_id = ris46.quest_comp_id AND ris46.numdom = 46 LEFT JOIN dott ris47 ON v2.quest_comp_id = ris47.quest_comp_id AND ris47.numdom = 47 LEFT JOIN dott ris48 ON v2.quest_comp_id = ris48.quest_comp_id AND ris48.numdom = 48 LEFT JOIN dott ris49 ON v2.quest_comp_id = ris49.quest_comp_id AND ris49.numdom = 49 LEFT JOIN dott ris50 ON v2.quest_comp_id = ris50.quest_comp_id AND ris50.numdom = 50 LEFT JOIN dott ris51 ON v2.quest_comp_id = ris51.quest_comp_id AND ris51.numdom = 51 LEFT JOIN dott ris52 ON v2.quest_comp_id = ris52.quest_comp_id AND ris52.numdom = 52 LEFT JOIN dott ris53 ON v2.quest_comp_id = ris53.quest_comp_id AND ris53.numdom = 53 LEFT JOIN dott ris54 ON v2.quest_comp_id = ris54.quest_comp_id AND ris54.numdom = 54 LEFT JOIN dott ris55 ON v2.quest_comp_id = ris55.quest_comp_id AND ris55.numdom = 55 LEFT JOIN dott ris56 ON v2.quest_comp_id = ris56.quest_comp_id AND ris56.numdom = 56 LEFT JOIN dott ris57 ON v2.quest_comp_id = ris57.quest_comp_id AND ris57.numdom = 57 LEFT JOIN dott ris58 ON v2.quest_comp_id = ris58.quest_comp_id AND ris58.numdom = 58 LEFT JOIN dott ris59 ON v2.quest_comp_id = ris59.quest_comp_id AND ris59.numdom = 59 LEFT JOIN dott ris60 ON v2.quest_comp_id = ris60.quest_comp_id AND ris60.numdom = 60 LEFT JOIN dott ris61 ON v2.quest_comp_id = ris61.quest_comp_id AND ris61.numdom = 61 LEFT JOIN dott ris62 ON v2.quest_comp_id = ris62.quest_comp_id AND ris62.numdom = 62 WHERE 1 = 1 AND v2.questionario_cod = 'QA_DOTTORANDI_STD'