TABLE transformée en tcd

Bonjour et merci d'avance ! ;)

J'ai cette table :
Pièce jointe 608780


que je voudrais transformer en çà :

Pièce jointe 608781


j'ai essayé ceci mais je suis un peu dépassé...

Code:

---

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

SELECT Données.IECSL/10000 as COUT, [A1], [A2], [B1], [B3], [C1], [C2], [D1] FROM ( select IELITM, IECOST, IECSL/10000 as COUT FROM PRODDTA.F30026 where IEMMCU LIKE '%02040' and IELEDG='07' and (IELITM = '500375'or IELITM = '509553') ) AS Données PIVOT ( AVG(Données.IELITM) FOR Données.IECOST IN ([A1], [A2], [B1], [B3], [C1], [C2], [D1]) ) AS PivotTable;

---

Pouvez-vous svp m'aider ?

Pour info je n'ai pris que 2 articles mais il y en a des milliers...

Merci

Bonjour,

Je n'ai pas l'habitude des PIVOT, alors je le fais en GROUP BY

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

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

---

Bonjour McM,

Merci pour ta réponse. Mais comment fais-tu lorsqu'il y a des milliers de ielitm ?

J'ai fini par trouver !

Code:

---

1 2 3 4

select * from (select IELITM, IECOST, IECSL/10000 as CT from PRODDTA.F30026 where IEMMCU LIKE '%02040' and IELEDG='07' and (IELITM = '500375'or IELITM = '509553')) pivot(max(CT) for IECOST in ('A1', 'A2', 'B1', 'B3', 'C1', 'C2', 'D1'));

---

Citation:

---

Envoyé par Chree

Bonjour McM,

Merci pour ta réponse. Mais comment fais-tu lorsqu'il y a des milliers de ielitm ?

---

Ca ne change rien à la requête. La seule différence est le nombre de colonne défini directement dans le select

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12

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 Ta_table WHERE .... GROUP BY ielitm

---