请问我在数据库中有以下数据code name
1001 aa
1002 bb
1003 cc
10011 aa1
10012 aa2
10013 aa3
10021 bb1
10022 bb2
10031 cc1如何转换成下列数据格式
code name code1 name1
10011 aa1 1001 aa
10012 aa2 1001 aa
10013 aa3 1001 aa
10021 bb1 1002 bb
10022 bb2 1002 bb
10031 cc1 1003 cc
1001 aa
1002 bb
1003 cc
10011 aa1
10012 aa2
10013 aa3
10021 bb1
10022 bb2
10031 cc1如何转换成下列数据格式
code name code1 name1
10011 aa1 1001 aa
10012 aa2 1001 aa
10013 aa3 1001 aa
10021 bb1 1002 bb
10022 bb2 1002 bb
10031 cc1 1003 cc
===========================sql================================ select tt2.code, tt2.name, tt1.code, tt1.name
from (select code, name
from (select tt_1.code,
tt_1.name,
length(tt_1.code) len_tt1,
min(length(tt_1.code)) over() min_id_1
from tablename tt_1)
where len_tt1 = min_id_1) tt1,
(select code, name
from (select tt_2.code,
tt_2.name,
length(tt_2.code) len_tt2,
min(length(tt_2.code)) over() min_id_2
from tablename tt_2)
where len_tt2 > min_id_2) tt2
where substr(tt2.code, 1, length(tt1.code)) = tt1.code;
==========================result==============================CODE NAME CODE NAME
----- ---- ----- ----
10011 aa1 1001 aa
10012 aa2 1001 aa
10013 aa3 1001 aa
10021 bb1 1002 bb
10022 bb2 1002 bb
10031 cc1 1003 cc6 rows selected
FROM TABLE1 T1,TABLE1 T2
WHERE INSTR(T1.CODE,T2.CODE)=1 AND T1.CODE<>T2.CODE;