数据表纵转横

编程技术  /  houtizong 发布于 3年前   71

数据表纵转横

方法一：

--创建函数与类型create or replace type str_tab is table of varchar2(32767);create public synonym str_tab for str_tabcreate or replace function col2row(pv in str_tab) return varchar2is  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;