Transposer des lignes en colonnes

**QuestVba** · 01/02/2021, 16h03

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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).
Nom : Image 35.png
Affichages : 140
Taille : 225,3 Ko

Nom : Image 35.png
Affichages : 140
Taille : 225,3 Ko

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...

**Waldar** · 01/02/2021, 16h10

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

**QuestVba** · 03/02/2021, 06h43

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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,

**JeitEmgie** · 03/02/2021, 08h42

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.

**QuestVba** · 03/02/2021, 14h08

JeitEmgie.
Merci mais pour le moment, nada, rien. Impossible d'avoir une avancée.

**Waldar** · 03/02/2021, 14h40

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).

**QuestVba** · 04/02/2021, 15h31

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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
;

Transposer des lignes en colonnes

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager