Summary of SQL statements for Oracle row-to-column and column-to-row Conversion

Summary of SQL statements for Oracle row-to-column and column-to-row Conversion

Convert multiple rows to strings

This is relatively simple. It can be implemented using the | or concat function.

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:: Aggregate (aggregate function for column name in (type), where an alias can be specified in ('') and in can also be specified as a subquery, such as select distinct code from MERs

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?

BytesThe XML clause in the operation can be used to solve this problem. This clause allows you to create outputs that execute the explain operation in XML format. In this output, you can specify a special clause ANY instead of a text value.

Example:

 

select * from (   select name, nums as "Purchase Frequency"   from demo t)                              pivot xml (   sum(nums) for name in (any))

As you can see, the NAME_XML column is XMLTYPE, where the root element is <effectset>. Each value is represented by a name-value element pair. You can use the output in any XML analyzer to generate more useful output.

 

 

Conclusion

 

BytesIt adds a very important and practical function to the SQL language. You can create a cross-tabulation report for any relational table using the functions, without having to write confusing and intuitive code that contains a large number of decode functions. Similarly, you can useUnregisterConvert any cross-table reports and store them as regular Relational Tables.BytesYou can generate output in common text or XML format. If the output is in XML format, you do not need to specify the value range to be searched by the distinct operation.