Oracle 四种列转行的方法

1. Oracle自带列转行函数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
)

--1
select nation, listagg(city, ',') within GROUP(order by city) all_citys
from temp
group by nation;

Result:

NATION	ALL_CITYS
China	Beijing,Guangzhou,Shanghai
Japan	Tokyo
USA	Bostom,New York

2. 自定义函数实现：

1) 定义函数string_agg：

CREATE OR REPLACE FUNCTION string_agg (p_input VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING t_string_agg;

2) 定义Type t_string_agg:

CREATE OR REPLACE TYPE "T_STRING_AGG" AS OBJECT
(
g_string VARCHAR2(32767),

STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_string_agg)
RETURN NUMBER,

MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_string_agg,
value IN VARCHAR2 )
RETURN NUMBER,

MEMBER FUNCTION ODCIAggregateTerminate(self IN t_string_agg,
returnValue OUT VARCHAR2,
flags IN NUMBER)
RETURN NUMBER,

MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_string_agg,
ctx2 IN t_string_agg)
RETURN NUMBER
);

3) 定义Type body:

CREATE OR REPLACE TYPE BODY "T_STRING_AGG" IS
STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_string_agg)
RETURN NUMBER IS
BEGIN
sctx := t_string_agg(NULL);
RETURN ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_string_agg,
value IN VARCHAR2 )
RETURN NUMBER IS
BEGIN
SELF.g_string := self.g_string || ',' || value;
RETURN ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateTerminate(self IN t_string_agg,
returnValue OUT VARCHAR2,
flags IN NUMBER)
RETURN NUMBER IS
BEGIN
returnValue := RTRIM(LTRIM(SELF.g_string, ','), ',');
RETURN ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_string_agg,
ctx2 IN t_string_agg)
RETURN NUMBER IS
BEGIN
SELF.g_string := SELF.g_string || ',' || ctx2.g_string;
RETURN ODCIConst.Success;
END;
END;

4） 实例：

--2
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, string_agg(city) all_citys from temp group by nation;

Result：

 

NATION	ALL_CITYS
China	Beijing,Guangzhou,Shanghai
Japan	Tokyo
USA	Bostom,New York

以下两种是针对大数据的行转列：

3. 针对大数据的行转列1：暂存入本地XML。

--3
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,
rtrim(extract(distinct xmlagg(xmlelement(e, city || ',')), '/E/text()')
.getclobval(),
',') all_citys
from temp
group by nation;

4. 自定义函数：

1） 定义Type：

CREATE OR REPLACE TYPE "T_VARCHAR2_TAB" AS TABLE OF VARCHAR2(4000);

2） 定义函数：

CREATE OR REPLACE FUNCTION tab_to_string (p_varchar2_tab IN t_varchar2_tab,
p_delimiter IN VARCHAR2 DEFAULT ',')
RETURN clob
PARALLEL_ENABLE
IS
l_string clob;
BEGIN
IF p_varchar2_tab.count>0 THEN
FOR i IN p_varchar2_tab.FIRST .. p_varchar2_tab.LAST LOOP
IF i != p_varchar2_tab.FIRST THEN
l_string := l_string || p_delimiter;
END IF;
l_string := l_string || p_varchar2_tab(i);
END LOOP;
END IF;
RETURN l_string;
END tab_to_string;

3) 实例：

--4
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,
tab_to_string(cast(collect(distinct city) as t_varchar2_tab)) all_citys
from temp
group by nation;