[SQL] Transformer une requête OneRowManyColumns vers OneColumnManyRows

Bonjour,

J'ai une requête comme celle-ci :

Code:

---

SELECT 'AAA' AS col1, 'BBB' AS col2, 'CCC' AS col3 FROM DUAL;

---

qui me retourne ceci :

Code:

---

1 2 3

COL1 COL2 COL3 ---- ---- ---- AAA BBB CCC

---

Et je souhaite obtenir cela :

Code:

---

1 2 3 4 5

COL4 ---- AAA BBB CCC

---

Comment faire ?
Merci.

Produit cartésien + CASE :

Code:

---

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

SQL> with t as ( 2 SELECT 'AAA' AS col1, 'BBB' AS col2, 'CCC' AS col3 FROM DUAL 3 ) 4 select case 5 when t2.rn = 1 then t.col1 6 when t2.rn = 2 then t.col2 7 when t2.rn = 3 then t.col3 8 end as c 9 from t 10 cross join (select level as rn from dual connect by level <= 3) t2;   C --- AAA BBB CCC

---

Ou UNPIVOT :

Code:

---

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

SQL> with t as ( 2 SELECT 'AAA' AS col1, 'BBB' AS col2, 'CCC' AS col3 FROM DUAL 3 ) 4 SELECT * 5 FROM t 6 UNPIVOT (c FOR col IN (col1, col2, col3));   COL C ---- --- COL1 AAA COL2 BBB COL3 CCC   SQL>

---

C'est exacterment ce que je cherchais :D

Merci skuatamad pour ta réponse rapide.

Ou avec UNION ALL :

Code:

---

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

WITH t AS ( SELECT 'AAA' AS col1, 'BBB' AS col2, 'CCC' AS col3 FROM DUAL ) select col1 as col4 from t UNION all select col2 from t UNION all select col3 from t;

---

Bonjour,
Avez-vous essayé d'utiliser la clause PIVOT de la 11g ?