0 0 0 0

Wm concat function

Function wm concat ( column name ) introduction: This function can separate the column values with a","sign, and display a row

Prepare test data :

createtable test(id number,name varchar2(20));insertinto test values(1,'a');insertinto test values(1,'b');insertinto test values(1,'c');insertinto test values(2,'d');insertinto test values(2,'e');

Effect 1: row, default comma
select wm_concat(name) name from test;
Effect 2: replace the comma in the result with"|""
select replace(wm_concat(name),',','|')from test;
Effect 3: merge name by id group
select id,wm_concat(name) name from test groupby id;

exte & ion usage:

Case: I'm going to write a view, like"create or replace view as select field 1. 50 from tablename", with 50 fields on base table, and if it's too cumbersome to write, don't have a simple way to do it?"if you're applying wm concat to make this requirement simple, let's say ( id, username, password, age ) 4 field. The query results are as follows

/** 这里的表名默认区分大小写 */select'create or replace view as select '|| wm_concat(column_name)||' from APP_USER' sqlStr 
 from user_tab_columns where table_name='APP_USER';

Oracle 11g row and column swap pivot and unpivot notes

Pivot column

createtable demo(id int,name varchar(20),nums int);---- 创建表 insertinto demo values(1,'苹果',1000);insertinto demo values(2,'苹果',2000);insertinto demo values(3,'苹果',4000);insertinto demo values(4,'橘子',5000);insertinto demo values(5,'橘子',3000);insertinto demo values(6,'葡萄',3500);insertinto demo values(7,'芒果',4200);insertinto demo values(8,'芒果',5500);

Row query

select*from(select name, nums from demo)pivot(sum(nums)for name in('苹果' 苹果,'橘子','葡萄','芒果'));
Note.Pivot ( aggregate function for column name in ), where in ( ') can specify alias, in can also specify subquery, such as select distinct code from customers

Of course, you can't use pivot functions, equivalent to the following statements, just a long code, and easy to understand.

------ 多项子查询 select*fromselectsum(nums) 苹果 from demo where name='苹果',selectsum(nums) 橘子 from demo where name='橘子',selectsum(nums) 葡萄 from demo where name='葡萄',selectsum(nums) 芒果 from demo where name='芒果';------ decode 函数利用 selectsum(decode(name,'苹果',nums)) 苹果,sum(decode(name,'橘子',nums)) 橘子, 
 sum(decode(name,'葡萄',nums)) 葡萄,sum(decode(name,'芒果',nums)) 芒果 from demo

Unpivot column

Case: Now there's a fruit table that records the number of sales of 4 quarter, and now it'll be shown in each quarter of each fruit.

Creating tables and data

createtable Fruit(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int);insertinto Fruit values(1,'苹果',1000,2000,3300,5000);insertinto Fruit values(2,'橘子',3000,3000,3200,1500);insertinto Fruit values(3,'香蕉',2500,3500,2200,2500);insertinto Fruit values(4,'葡萄',1500,2500,1200,3500);select*from Fruit
例子:select id , name, jidu, xiaoshou from Fruit unpivot(xiaoshou for jidu in(q1, q2, q3, q4))


Copyright © 2011 Dowemo All rights reserved.    Creative Commons   AboutUs