oracle语法列,ORACLE行转列经典语法

是否可以将多行变成一行

如：

A 1

B 2

C 3

效果为：A 1 B 2 C 3   不是用wm_concat 连接  而是形成6个独立字段

WITH x AS

(SELECT 'A' CODE_id,'1' code_value FROM dual

UNION

SELECT 'B' CODE_id,'2' code_value FROM dual

UNION

SELECT 'C' CODE_id,'3' code_value FROM dual

)

SELECT MAX(CASE WHEN RN=1 THEN CN END) CN1,

MAX(CASE WHEN RN=2 THEN CN END) CN2,

MAX(CASE WHEN RN=3 THEN CN END) CN3,

MAX(CASE WHEN RN=4 THEN CN END) CN4,

MAX(CASE WHEN RN=5 THEN CN END) CN5,

MAX(CASE WHEN RN=6 THEN CN END) CN6 FROM (

SELECT regexp_substr(a.name, '[^,]+', 1, LEVEL) AS CN,ROWNUM RN FROM (

SELECT wmsys.wm_concat(code_id||','||code_value) NAME from x

) A

CONNECT BY LEVEL <= (length(A.name) - length(REPLACE(A.name, ',', '')) + 1));