with t1 as( select 1 as A,2 as B,3 as C FROM DUAL union all select 4 as A,5 as B,6 as C FROM DUAL union all select 7 as A,8 as B,9 as C FROM DUAL ), t2 as( select 'A' as col1,(select wmsys.wm_concat(A) from t1 ) as col2 from dual union all select 'B' as col1,(select wmsys.wm_concat(B) from t1 ) as col2 from dual union all select 'C' as col1,(select wmsys.wm_concat(C) from t1 ) as col2 from dual ) select col1, REGEXP_SUBSTR(col2,'[^,]+',1,1) AS col2, REGEXP_SUBSTR(col2,'[^,]+',1,2) AS col3, REGEXP_SUBSTR(col2,'[^,]+',1,3) AS col4, REGEXP_SUBSTR(col2,'[^,]+',1,4) AS col5, REGEXP_SUBSTR(col2,'[^,]+',1,5) AS col6 from t2没有做排序,根据你实际要求加进去就可以。具体取多少记录你select多少列就可以。这是写死的。如果要写动态的话,单纯的sql语句实现不了
select 'A' ,A from table where A='A'
union all
select 'B' ,B from table where B='B'
union all
select 'C' ,C from table where C='C'
A B C D
访视日期
出生天数
体温
A B C
1 2 3
4 5 6
那么最后结果是
c1 c2 c3
A 1 4
这样的三列还是下面这样的两列?
c1 c2
A 1,4
select 1 as A,2 as B,3 as C FROM DUAL
union all
select 4 as A,5 as B,6 as C FROM DUAL
union all
select 7 as A,8 as B,9 as C FROM DUAL
),
t2 as(
select 'A' as col1,(select wmsys.wm_concat(A) from t1 ) as col2 from dual
union all
select 'B' as col1,(select wmsys.wm_concat(B) from t1 ) as col2 from dual
union all
select 'C' as col1,(select wmsys.wm_concat(C) from t1 ) as col2 from dual
)
select col1,
REGEXP_SUBSTR(col2,'[^,]+',1,1) AS col2,
REGEXP_SUBSTR(col2,'[^,]+',1,2) AS col3,
REGEXP_SUBSTR(col2,'[^,]+',1,3) AS col4,
REGEXP_SUBSTR(col2,'[^,]+',1,4) AS col5,
REGEXP_SUBSTR(col2,'[^,]+',1,5) AS col6
from t2没有做排序,根据你实际要求加进去就可以。具体取多少记录你select多少列就可以。这是写死的。如果要写动态的话,单纯的sql语句实现不了