数据库中经常用到行列转化，不同的数据库实现方式稍微不同，在里面写下达梦的实现方式。

列转行：

第一种：通过 listagg 实现

with temp as(  
  select 'China' nation ,'Guangzhou' city from dual union all  
  select 'China' nation ,'Shanghai' city from dual union all  
  select 'China' nation ,'Beijing' city from dual union all  
  select 'USA' nation ,'New York' city from dual union all  
  select 'USA' nation ,'Bostom' city from dual union all  
  select 'Japan' nation ,'Tokyo' city from dual   
)  
select nation,listagg(city,',') within GROUP (order by city)  
from temp  
group by nation ;

第二种：通过 wm_concat 实现

with temp as(  
  select 'China' nation ,'Guangzhou' city from dual union all  
  select 'China' nation ,'Shanghai' city from dual union all  
  select 'China' nation ,'Beijing' city from dual union all  
  select 'USA' nation ,'New York' city from dual union all  
  select 'USA' nation ,'Bostom' city from dual union all  
  select 'Japan' nation ,'Tokyo' city from dual   
)  
select nation,wm_concat(city )  
from temp  
group by nation ;

行转列

既行拆分为多列
创建测试表并插入数据：

CREATE TABLE "SYSDBA"."TEST123"
(
"ID" INT,
"NAME1" VARCHAR2(100)) ;
insert into "SYSDBA"."TEST123"("ID", "NAME1") 
VALUES(1,'03,05'),
(2,'03,05'),
(3,'01,02')
;
commit;

遍历全表：

select id,regexp_substr(name1,'[^,]+',1,level) data 
from test123 
connect by prior id=id and level<=regexp_count(name1,',')+1 and prior dbms_random.value is not null;

根据某一id查询：

SELECT id,REGEXP_SUBSTR(name1, '[^,]+', 1, LEVEL )   STR    
FROM test123  where id = 1 
CONNECT BY LEVEL <= regexp_count(name1,',')+1