Summary of SQL statements for Oracle row-to-column and column-to-row, oraclesql

Summary of SQL statements for Oracle row-to-column and column-to-row, oraclesql

Reference: Functions | or concat functions can be implemented.

select concat(id,username) str from app_userselect id||username str from app_user

Converting strings to multiple columns is actually a matter of splitting strings. You can use the substr, instr, regexp_substr functions to convert strings to multiple rows using the union all function.
Wm_concat Function

First, let's take a look at this magic function.Wm_concat(Column name). This function can separate the column values with "," and display them as a row. Next, let's take a look at how this magic function can be used to prepare test data.

create table test(id number,name varchar2(20));insert into test values(1,'a');insert into test values(1,'b');insert into test values(1,'c');insert into test values(2,'d');insert into test values(2,'e');

Effect 1: Convert rows to columns, separated by commas by default

select wm_concat(name) name from test;

Result 2: replace the comma in the result with "|"

select replace(wm_concat(name),',','|') from test;

Result 3: name is merged by ID Group

select id,wm_concat(name) name from test group by id;

The SQL statement is equivalent to the following SQL statement.

-------- Applicability: 8i, 9i, 10 GB and later (MAX + DECODE) select id, max (decode (rn, 1, name, null )) | max (decode (rn, 2, ',' | name, null) | max (decode (rn, 3, ',' | name, null )) str from (select id, name, row_number () over (partition by id order by name) as rn from test) t group by id order by 1; -------- Applicability: 8i, 9i, 10g and later versions (ROW_NUMBER + LEAD) select id, str from (select id, row_number () over (partition by id order by name) as rn, name | lead (',' | name, 1) over (partition by id order by name) | lead (',' | name, 2) over (partition by id order by name) | lead (',' | name, 3) over (partition by id order by name) as str from test) where rn = 1 order by 1; -------- Applicability: 10 GB and later (MODEL) select id, substr (str, 2) str from test model return updated rows partition by (id) dimension by (row_number () over (partition by id order by name) as rn) measures (cast (name as varchar2 (20) as str) rules upsert iterate (3) until (presentv (str [iteration_number + 2], 1, 0) = 0) (str [0] = str [0] | ',' | str [iteration_number + 1]) order by 1; -------- Applicability: 8i, 9i, 10 GB and later (MAX + DECODE) select t. id, max (substr (sys_connect_by_path (t. name, ','), 2) str from (select id, name, row_number () over (partition by id order by name) rn from test) t start with rn = 1 connect by rn = prior rn + 1 and id = prior id group by t. id; </span>

Lazy extension usage: Case:I want to write a view, similar to "create or replace view as select Field 1 ,... field 50 from tablename ", the base table has more than 50 fields. If it is too difficult to write manually, is there any easy way? Of course, if I use wm_concat to simplify this requirement, let's assume that my APP_USER table has four fields (id, username, password, age. The query result is as follows:

/** The table name here is case sensitive by default */select 'create or replace view as select' | wm_concat (column_name) | 'from APP_USER 'sqlStr from user_tab_columns where table_name = 'app _ user ';

Query using system tables

select * from user_tab_columns

Description of Oracle 11g row-and-column swap and unregister

In Oracle 11g, Oracle adds two more queries:Bytes(Row-to-column) andUnregister(Column-to-row)

Reference: callback

Google, there is a more detailed document online: http://www.oracle-developer.net/display.php? Id = 506

The testing data (id, type name, sales quantity) in the rows column. Case: A piece of data is queried Based on the fruit type to show the sales quantity of each type.

Create table demo (id int, name varchar (20), nums int); ---- create table insert into demo values (1, 'apple', 1000 ); insert into demo values (2, 'apple', 2000); insert into demo values (3, 'apple', 4000); insert into demo values (4, 'orange ', 5000); insert into demo values (5, 'orange', 3000); insert into demo values (6, 'Grape ', 3500); insert into demo values (7, 'mango', 4200); insert into demo values (8, 'mango', 5500 );

Group query (Of course, this does not meet the requirements for querying a piece of data)

select name, sum(nums) nums from demo group by name

Row-to-column Query

Select * from (select name, nums from demo) values (sum (nums) for name in ('apple' apple, 'orange', 'Grape, 'mango '));

Note:: Alias (aggregate function for column name in (type), where the alias can be specified in ('')

You can also choose not to use the explain function, which is equivalent to the following statements, but the code is long and easy to understand.

Select * from (select sum (nums) Apple from demo where name = 'apple'), (select sum (nums) orange from demo where name = 'orange '), (select sum (nums) grape from demo where name = 'Grape '), (select sum (nums) mango from demo where name = 'mango ');

As the name implies, the unordered row-to-column conversion function converts multiple columns into one column.
Case:Now there is a fruit table that records the sales volume in four quarters. Now we need to display the sales volume of each fruit in multiple rows.

Create tables and data

Create table Fruit (id int, name varchar (20), Q1 int, Q2 int, Q3 int, Q4 int); insert into Fruit values (1, 'apple', 2000, ); insert into Fruit values (2, 'orange',); insert into Fruit values (3, 'Banana ); insert into Fruit values (4, 'Grape ',); select * from Fruit

Column-to-row Query

select id , name, jidu, xiaoshou from Fruit unpivot (xiaoshou for jidu in (q1, q2, q3, q4) )

Note: The unaggregate function does not exist. The xiaoshou and jidu fields are also temporary variables.

Similarly, the same effect can be achieved without unblocking, but the SQL statement will be very long, and the execution speed is not as high as the former.

select id, name ,'Q1' jidu, (select q1 from fruit where id=f.id) xiaoshou from Fruit funionselect id, name ,'Q2' jidu, (select q2 from fruit where id=f.id) xiaoshou from Fruit funionselect id, name ,'Q3' jidu, (select q3 from fruit where id=f.id) xiaoshou from Fruit funionselect id, name ,'Q4' jidu, (select q4 from fruit where id=f.id) xiaoshou from Fruit f

XML type in the preceding example, you already know the types to be queried, including in (). If you do not know the values, how do you build a query?