Transposer des lignes en colonnes

Version imprimable

1 pièce(s) jointe(s)

Bonjour à tous,
J'espère que tout le monde se porte bien.

J'ai la requête suivante qui me donne une liste de dossiers, de NISS de personnes de référence (colonne C) et le (ou les) COHAB de cette personne (en colonne D).
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 DEFINE my_period = 202101 SELECT DISTINCT &&my_period AS mois_réf ,chi.file_number AS dossier ,per.inss AS niss ,CASE WHEN fam.type LIKE 'D%' THEN per4.inss ELSE NULL END AS cohab FROM children chi INNER JOIN legal_situation leg ON leg.child_id = chi.actor_id AND (&&my_period BETWEEN (leg.start_year * 100 + leg.start_month) AND NVL(leg.end_year * 100 + leg.end_month,999999)) AND leg.status = 'ACTIVE' INNER JOIN childbeneficiarylinks cbl ON cbl.child_id = chi.actor_id AND (&&my_period BETWEEN (cbl.start_year * 100 + cbl.start_month) AND NVL(cbl.end_year * 100 + cbl.end_month,999999)) INNER JOIN actors ac ON ac.actor_id = cbl.beneficiary_id INNER JOIN persons per ON per.person_id = ac.person_id INNER JOIN historical_family_situations his_fam ON his_fam.concerned_natural_person_id = ac.person_id INNER JOIN family_situations fam ON his_fam.historical_fam_situation_id = fam.historical_fam_situation_id AND TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(&&my_period, 'yyyymm'), -1)),'yyyymmdd')) BETWEEN TO_NUMBER(TO_CHAR(fam.start_date,'yyyymmdd')) AND NVL(TO_NUMBER(TO_CHAR(fam.end_date,'yyyymmdd')),99991231) LEFT JOIN persons per4 ON per4.person_id = fam.second_natural_person_id ORDER BY 3, 4 ;
Soit cette personne habite seule et je ne la trouve qu'une fois dans ma liste sans donnée dans la colonne D. OK (ligne 4)
Soit cette personne est en ménage avec une seule autre personne et je ne la trouve également qu'une fois dans ma liste avec le mention du cohabitant dans la colonne D. OK (ligne 1)
Soit cette personne est en ménage avec plus d'une personne et alors, j'ai autant de lignes que de cohabitant. C'est ici que je voudrais de l'aide si possible. Voici le schéma en Excel de ce que j'ai et de ce que je voudrais en colonnes G→O (car il y a au max 6 cohabitants) (lignes 9-10 ou lignes 12-13-14).
Pièce jointe 589858
Ma question : est-il possible d'avoir une seule ligne par personnes de référence et, à la queue leu-leu, les éventuels cohabitants sur la même ligne ?
Un grand merci pour les pistes...

01/02/2021, 16h10
Waldar

Faites une petite recherche sur le mot clef PIVOT, vous devriez trouver votre bonheur.

Bonjour Waldar,
J'ai suivi ton idée de PIVOT et voici mon sql.

Code:

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
29
30
31
32
33
34
35
36
37
38
39
40
DEFINE my_period = 202101
 
SELECT *
FROM
	(
		SELECT 
			CASE WHEN cohab IS NULL THEN 0 ELSE 
				ROW_NUMBER() OVER(PARTITION BY dossier, niss ORDER BY cohab) END AS POSITION
			, mois_réf
			, dossier
			, niss
			, cohab
		FROM 
			(
			SELECT DISTINCT
			    &&my_period AS mois_réf
			    ,chi.file_number AS dossier
			    ,per.inss AS niss
			    ,CASE WHEN fam.type LIKE 'D%' THEN per4.inss ELSE NULL END AS cohab
			FROM children chi
			    INNER JOIN legal_situation leg ON leg.child_id = chi.actor_id
			        AND (&&my_period BETWEEN (leg.start_year * 100 + leg.start_month) AND NVL(leg.end_year * 100 + leg.end_month,999999))
			        AND leg.status = 'ACTIVE'
			    INNER JOIN childbeneficiarylinks cbl ON cbl.child_id = chi.actor_id
			        AND (&&my_period BETWEEN (cbl.start_year * 100 + cbl.start_month) AND NVL(cbl.end_year * 100 + cbl.end_month,999999))
			    INNER JOIN actors ac ON ac.actor_id = cbl.beneficiary_id
			    INNER JOIN persons per ON per.person_id = ac.person_id
			    INNER JOIN historical_family_situations his_fam ON his_fam.concerned_natural_person_id = ac.person_id
			    INNER JOIN family_situations fam ON his_fam.historical_fam_situation_id = fam.historical_fam_situation_id
			        AND TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(&&my_period, 'yyyymm'), -1)),'yyyymmdd')) BETWEEN TO_NUMBER(TO_CHAR(fam.start_date,'yyyymmdd')) AND NVL(TO_NUMBER(TO_CHAR(fam.end_date,'yyyymmdd')),99991231)
			    LEFT JOIN persons per4 ON per4.person_id = fam.second_natural_person_id
			ORDER BY 3, 4
			)
	)
PIVOT
	(
	SUM(COHAB)
	FOR POSITION IN (1,2,3,4,5,6)
	)
;

J'ai plusieurs questions :

Est-ce correct la manière que j'utilise ? Je trouve étrange le SUM qui n'est pas vraiment un SUM.
La seule manière de faire que j'ai trouvée, c'est de mêler ce PIVOT avec un ROW_NUMBER() OVER(PARTITION BY. Est-e correct ?
Pour le moment, j'ai mis FOR POSITION IN (1,2,3,4,5,6) mais que se passera-t-il le jour au j'aurais un cas avec 7 mises en ménage ? Est-il possible d'automatiser cette partie pour détecter ce 7e ?

Oui, je sais, j'ai plein de questions mais comme je suis débutant, je dois comprendre pour évoluer. Merci,

03/02/2021, 08h42
JeitEmgie
Citation:
Pour le moment, j'ai mis FOR POSITION IN (1,2,3,4,5,6) mais que se passera-t-il le jour au j'aurais un cas avec 7 mises en ménage ? Est-il possible d'automatiser cette partie pour détecter ce 7e ?
La clause IN de PIVOT n'est pas dynamique (mais celle de XML_PIVOT l'est) et vous trouverez sur le Net ou dans Live SQL différentes méthodes pour contourner le problème,
de la plus simple utilisant EXECUTE IMMEDIATE à la plus sophistiquée utilisant les PTF.
03/02/2021, 14h08
QuestVba

JeitEmgie.
Merci mais pour le moment, nada, rien. Impossible d'avoir une avancée.
03/02/2021, 14h40
Waldar
Citation:
Envoyé par QuestVba

J'ai plusieurs questions :

Est-ce correct la manière que j'utilise ? Je trouve étrange le SUM qui n'est pas vraiment un SUM.
La seule manière de faire que j'ai trouvée, c'est de mêler ce PIVOT avec un ROW_NUMBER() OVER(PARTITION BY. Est-e correct ?
Pour le moment, j'ai mis FOR POSITION IN (1,2,3,4,5,6) mais que se passera-t-il le jour au j'aurais un cas avec 7 mises en ménage ? Est-il possible d'automatiser cette partie pour détecter ce 7e ?

Oui, je sais, j'ai plein de questions mais comme je suis débutant, je dois comprendre pour évoluer. Merci,
Oui c'est très bien, j'aurai écrit quelque chose de similaire.
Bravo, ça fait plaisir !

Pour la partie dynamique, effectivement il vous faut une des solutions proposées par JeitEmgie. Ou alors vous prévoyez jusqu'à 10 et vous supervisez si ça évolue plus vite que prévu (c'est moins bien mais plus simple).

Re- à tous,

Voici mon résultat. Le seul ennui est que je dois faire le traitement en deux étapes et que je dois encoder la valeur du pop-up représentant str_in_statement. Y a-t-il un moyen d'automatiser le tout en une seule requête ?

Code:

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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
UNDEFINE str_in_statement;
DEFINE my_period = 202101;
clear columns;
column temp_in_statement new_value str_in_statement;
select 
  distinct listagg(position||' as VAL_'||position,',')
  within group (order by position) as temp_in_statement
from (
        SELECT distinct
            CASE WHEN cohab IS NULL THEN 0 ELSE 
                ROW_NUMBER() OVER(PARTITION BY dossier, niss ORDER BY cohab) END AS POSITION
        FROM 
            (
            SELECT DISTINCT
                &&my_period AS mois_réf
                ,chi.file_number AS dossier
                ,per.inss AS niss
                ,CASE WHEN fam.type LIKE 'D%' THEN per4.inss ELSE NULL END AS cohab
            FROM children chi
                INNER JOIN legal_situation leg ON leg.child_id = chi.actor_id
                    AND (&&my_period BETWEEN (leg.start_year * 100 + leg.start_month) AND NVL(leg.end_year * 100 + leg.end_month,999999))
                    AND leg.status = 'ACTIVE'
                INNER JOIN childbeneficiarylinks cbl ON cbl.child_id = chi.actor_id
                    AND (&&my_period BETWEEN (cbl.start_year * 100 + cbl.start_month) AND NVL(cbl.end_year * 100 + cbl.end_month,999999))
                INNER JOIN actors ac ON ac.actor_id = cbl.beneficiary_id
                INNER JOIN persons per ON per.person_id = ac.person_id
                INNER JOIN historical_family_situations his_fam ON his_fam.concerned_natural_person_id = ac.person_id
                INNER JOIN family_situations fam ON his_fam.historical_fam_situation_id = fam.historical_fam_situation_id
                    AND TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(&&my_period, 'yyyymm'), -1)),'yyyymmdd')) BETWEEN TO_NUMBER(TO_CHAR(fam.start_date,'yyyymmdd')) AND NVL(TO_NUMBER(TO_CHAR(fam.end_date,'yyyymmdd')),99991231)
                LEFT JOIN persons per4 ON per4.person_id = fam.second_natural_person_id
            ORDER BY 3, 4
            )
);
 
 
 
 
SELECT *
FROM
    (
        SELECT 
            CASE WHEN cohab IS NULL THEN 0 ELSE 
                ROW_NUMBER() OVER(PARTITION BY dossier, niss ORDER BY cohab) END AS POSITION
            , mois_réf
            , dossier
            , niss
            , cohab
        FROM 
            (
            SELECT DISTINCT
                &&my_period AS mois_réf
                ,chi.file_number AS dossier
                ,per.inss AS niss
                ,CASE WHEN fam.type LIKE 'D%' THEN per4.inss ELSE NULL END AS cohab
            FROM children chi
                INNER JOIN legal_situation leg ON leg.child_id = chi.actor_id
                    AND (&&my_period BETWEEN (leg.start_year * 100 + leg.start_month) AND NVL(leg.end_year * 100 + leg.end_month,999999))
                    AND leg.status = 'ACTIVE'
                INNER JOIN childbeneficiarylinks cbl ON cbl.child_id = chi.actor_id
                    AND (&&my_period BETWEEN (cbl.start_year * 100 + cbl.start_month) AND NVL(cbl.end_year * 100 + cbl.end_month,999999))
                INNER JOIN actors ac ON ac.actor_id = cbl.beneficiary_id
                INNER JOIN persons per ON per.person_id = ac.person_id
                INNER JOIN historical_family_situations his_fam ON his_fam.concerned_natural_person_id = ac.person_id
                INNER JOIN family_situations fam ON his_fam.historical_fam_situation_id = fam.historical_fam_situation_id
                    AND TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(&&my_period, 'yyyymm'), -1)),'yyyymmdd')) BETWEEN TO_NUMBER(TO_CHAR(fam.start_date,'yyyymmdd')) AND NVL(TO_NUMBER(TO_CHAR(fam.end_date,'yyyymmdd')),99991231)
                LEFT JOIN persons per4 ON per4.person_id = fam.second_natural_person_id
            ORDER BY 3, 4
            )
    )
PIVOT
    (
    SUM(COHAB) 
    FOR POSITION IN (&&str_in_statement)
    )
--where VAL_6 is not null
;