数据表纵转横

数据表纵转横

方法一：

--创建函数与类型

create or replace type str_tab is table of varchar2(32767);
create public synonym str_tab for str_tab

create or replace function col2row(pv in str_tab) return varchar2
is
  ls varchar2(32767);
begin
  for i in 1..pv.count loop
      ls := ls || pv(i)||'，';
  end loop;
  return ls;
end;



select 
       col2row(cast(multiset (select  columnName from tableName) as str_tab)) as myName
                        from dual

select columnName from tableName结果不要太多。

MULTISET 必须和 CASTs 一起使用，MULTISET 将数据集转换为 collection，SQL MULTISET function 和操作 nested tables 的 PL/SQL MULTISET 是完全不同的
SELECT CAST (MULTISET (SELECT field FROM table) AS collection-type)
FROM DUAL;
As with the CAST pseudo-function, MULTISET cannot serve as the target of an INSERT, UPDATE, or DELETE statement.

 

 

方法二：

我们通过 oracle 10g 所提供的 WMSYS.WM_CONCAT 函数即可以完成 行转列的效果
select t.rank, WMSYS.WM_CONCAT(t.Name) TIME From t_menu_item t GROUP BY t.rank;