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
| WITH T AS (SELECT 500375 ielitm, 'A1' iecost, 0.2057 cout FROM dual
UNION SELECT 509553 ielitm, 'A1' iecost, 23.1253 cout FROM dual
UNION SELECT 509553 ielitm, 'A2' iecost, 0.0048 cout FROM dual
UNION SELECT 509553 ielitm, 'B1' iecost, 44.6071 cout FROM dual
UNION SELECT 500375 ielitm, 'B1' iecost, 2.2367 cout FROM dual
UNION SELECT 500375 ielitm, 'B3' iecost, 1.1209 cout FROM dual
UNION SELECT 509553 ielitm, 'B3' iecost, 19.3165 cout FROM dual
UNION SELECT 509553 ielitm, 'C1' iecost, 20.9414 cout FROM dual
UNION SELECT 500375 ielitm, 'C1' iecost, 1.6429 cout FROM dual
UNION SELECT 509553 ielitm, 'C2' iecost, 38.0312 cout FROM dual
UNION SELECT 500375 ielitm, 'C2' iecost, 2.2504 cout FROM dual
UNION SELECT 509553 ielitm, 'D1' iecost, 650.2914 cout FROM dual)
SELECT ielitm,
MAX(CASE WHEN iecost = 'A1' THEN cout END) AS A1,
MAX(CASE WHEN iecost = 'A2' THEN cout END) AS A2,
MAX(CASE WHEN iecost = 'B1' THEN cout END) AS B1,
MAX(CASE WHEN iecost = 'B3' THEN cout END) AS B3,
MAX(CASE WHEN iecost = 'C1' THEN cout END) AS C1,
MAX(CASE WHEN iecost = 'C2' THEN cout END) AS C2,
MAX(CASE WHEN iecost = 'D1' THEN cout END) AS D1,
SUM(CASE WHEN iecost NOT IN ('A1', 'A2', 'B1', 'B3', 'C1', 'C2', 'D1') THEN cout END) AS Autres
FROM T
GROUP BY ielitm
IELITM A1 A2 B1 B3 C1 C2 D1 AUTRES
500375 0.2057 null 2.2367 1.1209 1.6429 2.2504 null null
509553 23.1253 0.0048 44.6071 19.3165 20.9414 38.0312 650.2914 null |
Partager