ROLLUP, CUBE ou PIVOT ?

Bonjour,
soit une table T1 donnée

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 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142

CREATE TABLE T1 (COL01 VARCHAR2(05),COL02 VARCHAR2(05),COL03 VARCHAR2(05),COL04 VARCHAR2(05));   INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '001', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '001', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '001', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '001', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '001', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '001', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '001', '25', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '001', '25', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '001', '25', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '001', '25', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '001', '25', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '001', '25', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '001', '25', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('00', '005', '20', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '001', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '001', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '001', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '001', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '001', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '001', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '005', '20', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '005', '20', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '005', '20', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '005', '20', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '006', '80', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '006', '80', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '006', '80', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '006', '80', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '006', '80', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '006', '80', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '006', '80', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('01', '006', '80', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('02', '001', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('02', '001', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('02', '001', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('02', '001', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('02', '001', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('02', '001', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('03', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('03', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('03', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('03', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('03', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('03', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('03', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('03', '002', '01', '1'); INSERT INTO T1 (COL01, COL02, COL03, COL04) VALUES ('03', '002', '01', '1');

---

Pourriez-vous m'aider à approcher le plus possible du résultat suivant (=tableau croisé EXCEL) via un SELECT et\ou une procédure PL\SQL ?

http://www.freeimagehosting.net/1x2it

http://www.freeimagehosting.net/1x2it
J’ai tenté un ROLLUP mais c’est toujours pas ce qu’il me faut.

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

SELECT DECODE(GROUPING(COL01), 1, 'ALL COL01', COL01) AS COL01, DECODE(GROUPING(COL02), 1, 'ALL COL02', COL02) AS COL02, DECODE(GROUPING(COL03), 1, 'ALL COL03', COL03) AS COL03, COUNT(COL04) AS COUNT_COL04 FROM T1 GROUP BY ROLLUP (COL01,COL02,COL03) ORDER BY COL01,COL02,COL03;     COL01 COL02 COL03 COUNT_COL04 --------- --------- --------- ----------- ALL COL01 ALL COL02 ALL COL03 140 00 ALL COL02 ALL COL03 52 00 001 ALL COL03 13 00 001 01 6 00 001 25 7 00 002 ALL COL03 38 00 002 01 38 00 005 ALL COL03 1 00 005 20 1 01 ALL COL02 ALL COL03 73 01 001 ALL COL03 6 01 001 01 6 01 002 ALL COL03 55 01 002 01 55 01 005 ALL COL03 4 01 005 20 4 01 006 ALL COL03 8 01 006 80 8 02 ALL COL02 ALL COL03 6 02 001 ALL COL03 6 02 001 01 6 03 ALL COL02 ALL COL03 9 03 002 ALL COL03 9 03 002 01 9

---

Hello,

Regarde ce poste:http://www.developpez.net/forums/d12...lonne-via-sql/

Jko

Il y a aussi un pivot à inclure dans la requête et un peu de mise en forme :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

select case when grouping(col01) = 1 then 'Grand Total' when grouping(col02) = 1 then col01 else ' ' || col02 end as Raw_Label , count(case col03 when '01' then 1 end) as "01" , count(case col03 when '20' then 1 end) as "20" , count(case col03 when '25' then 1 end) as "25" , count(case col03 when '80' then 1 end) as "80" , count(*) as "Grand Total" from t1 group by rollup(col01, col02) order by grouping(t1.col01) asc , col01 asc , grouping(t1.col02) desc , col02 asc;

---

Résultat :

  RAW_LABEL   01   20   25   80 Grand Total
----------- ---- ---- ---- ---- -----------
00            44    1    7    0          52
    001        6    0    7    0          13
    002       38    0    0    0          38
    005        0    1    0    0           1
01            61    4    0    8          73
    001        6    0    0    0           6
    002       55    0    0    0          55
    005        0    4    0    0           4
    006        0    0    0    8           8
02             6    0    0    0           6
    001        6    0    0    0           6
03             9    0    0    0           9
    002        9    0    0    0           9
Grand Total  120    5    7    8         140

Merci Waldar, j’étais arrivé au même résultat que toi

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

WITH DATAS AS ( SELECT * FROM T1 PIVOT ( COUNT(COL04) FOR COL03 IN ('01' AS V_01,'20' AS V_02,'25' AS V_03,'80' AS V_04) ) ORDER BY COL01,COL02 ) SELECT DECODE(GROUPING(COL01), 1, 'NULL', COL01) AS K_01, DECODE(GROUPING(COL02), 1, 'NULL', COL02) AS K_02, SUM(V_01) AS V_01, SUM(V_02) AS V_02, SUM(V_03) AS V_03, SUM(V_04) AS V_04, SUM(V_01+V_02+V_03+V_04) AS SUM_SUM FROM DATAS GROUP BY ROLLUP (COL01,COL02) ORDER BY COL01,COL02;   K_01 K_02 V_01 V_02 V_03 V_04 SUM_SUM ----- ----- ---- ---- ---- ---- ------- 00 001 6 0 7 0 13 00 002 38 0 0 0 38 00 005 0 1 0 0 1 00 NULL 44 1 7 0 52 01 001 6 0 0 0 6 01 002 55 0 0 0 55 01 005 0 4 0 0 4 01 006 0 0 0 8 8 01 NULL 61 4 0 8 73 02 001 6 0 0 0 6 02 NULL 6 0 0 0 6 03 002 9 0 0 0 9 03 NULL 9 0 0 0 9 NULL NULL 120 5 7 8 140

---

Le problème est que je connais pas les différentes valeurs de COL03.
Dans ce cas de figure, j'ai bien vu qu'il existe la fonction PIVOT XML mais je ne vois pas comment exploiter ce résultat

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

SELECT * FROM T1 PIVOT XML( COUNT(COL04) AS COUNT_COL04 FOR COL03 IN (ANY) ) ORDER BY COL01,COL02   COL01 COL02 COL03_XML ----- ----- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 00 001 <PivotSet><item><column name = "COL03">01</column><column name = "COUNT(COL04)">6</column></item><item><column name = "COL03">25</column><column name = "COUNT(COL04)">7</column></item></PivotSet> 00 002 <PivotSet><item><column name = "COL03">01</column><column name = "COUNT(COL04)">38</column></item></PivotSet> 00 005 <PivotSet><item><column name = "COL03">20</column><column name = "COUNT(COL04)">1</column></item></PivotSet> 01 001 <PivotSet><item><column name = "COL03">01</column><column name = "COUNT(COL04)">6</column></item></PivotSet> 01 002 <PivotSet><item><column name = "COL03">01</column><column name = "COUNT(COL04)">55</column></item></PivotSet> 01 005 <PivotSet><item><column name = "COL03">20</column><column name = "COUNT(COL04)">4</column></item></PivotSet> 01 006 <PivotSet><item><column name = "COL03">80</column><column name = "COUNT(COL04)">8</column></item></PivotSet> 02 001 <PivotSet><item><column name = "COL03">01</column><column name = "COUNT(COL04)">6</column></item></PivotSet> 03 002 <PivotSet><item><column name = "COL03">01</column><column name = "COUNT(COL04)">9</column></item></PivotSet>

---

J'en conclus qu'il faudra construire dynamiquement ma requête

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

WITH DATAS AS ( SELECT * FROM T1 PIVOT ( COUNT(COL04) FOR COL03 IN (....) ) ORDER BY COL01,COL02 ) SELECT DECODE(GROUPING(COL01), 1, 'NULL', COL01) AS K_01, DECODE(GROUPING(COL02), 1, 'NULL', COL02) AS K_02, ... SUM(...) AS SUM_SUM FROM DATAS GROUP BY ROLLUP (COL01,COL02) ORDER BY COL01,COL02;

---