Summary of SQL statements for Oracle row-to-column, column-changing lines (GO)

Multi-line Turn string

This is relatively simple, with | | Or the CONCAT function can be implemented

Select Concat (id,username) str from App_userselect id| | Username Str from App_user

String to multiple columns

is actually the problem of splitting the string, you can use the substr, InStr, Regexp_substr function mode

String to multiple lines

Use the UNION ALL function, and so on

Wm_concat function

First let's take a look at this magical function wm_concat(column name), which separates the column values by the "," number, and displays them as a line, and then the example below to see how this magical function is applied to prepare the test data

CREATE TABLE Test (ID number,name varchar2); INSERT into test values (1, ' a '); INSERT into test values (1, ' B '); INSERT INTO t EST values (1, ' C '); INSERT into test values (2, ' d '); INSERT into test values (2, ' e ');

Effect 1: Row to column, comma separated by default

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: Group by ID merge name

Select Id,wm_concat (name) name from the test group by ID;

The SQL statement is equivalent to the following SQL statement

--------Scope of application: 8i,9i,10g 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 R      n from test) T-GROUP BY-ID order by 1; --------Scope of application: 8i,9i,10g and later (Row_number + lead) Select ID, str from (select Id,row_number () over (partition by ID order b Y 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; --------Scope of application: 10g and later version (MODEL) Select ID, substr (str, 2) str from test MODEL return updated rows partition by (ID) dimensi On by (Row_number ()-Partition by-ID order by name) as RN measures (CAST (name as VARCHAR2) as STR) Rules Upsert it Erate (3) until (Presentv (str[iteration_number+2],1,0) =0) (str[0] = str[0] | | ', ' | | Str[iteRATION_NUMBER+1]) Order by 1; --------applicable to: 8i,9i,10g and later versions (MAX + DECODE) Select T.id Id,max (substr (Sys_connect_by_path (T.name, ', '), 2)) Str from (Sele CT ID, name, row_number () over (partition by ID order by name) rn from test) T-start with RN = 1 Connect by RN = Prio R 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 from TableName ", the base table has more than 50 fields, if it is too cumbersome to write by hand, is there any easy way? Sure, look at me. If you apply wm_concat to make this requirement simple, let's say that there are (Id,username,password,age) 4 fields in my App_user table. The query results are as follows

/** table names are 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 ';

Querying using system table mode

SELECT * FROM User_tab_columns

Oracle 11g row and column interchange pivot and UNPIVOT instructions

In Oracle 11g, Oracle has added 2 more queries:pivot(row to column) and unpivot(column change)

Reference: http://blog.csdn.net/tianlesoftware/article/details/7060306, http://www.oracle.com/technetwork/cn/articles/ 11g-pivot-101924-zhs.html

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

Pivot Column Career

Test data (ID, type name, sales quantity), Case: A data query based on the type of fruit shows the quantity of each type of sales.

CREATE TABLE demo (ID int,name varchar (), nums int);  ----CREATE TABLE INSERT into demo values (1, ' Apple ', + +), insert into demo values (2, ' Apple ', "+"), insert into demo values (3, ' Apple ', 400 0) insert into demo values (4, ' Orange ', ' n '), insert into demo values (5, ' orange ', ' + '), insert into demo values (6, ' grapes ', 3500); Sert into demo values (7, ' Mango ', 4200); INSERT into demo values (8, ' Mango ', 5500);

Group Queries ( This is not a requirement for querying a single piece of data, of course)

Select Name, sum (nums) Nums from demo GROUP by name

Row-to-column query

SELECT * FROM [select Name, Nums from demo] Pivot (SUM (nums) for name in (' Apple ' apple, ' orange ', ' grape ', ' mango '));

Note : Pivot (aggregate function for column name in (type)), where in (") you can specify an alias, in can also specify subqueries, such as select distinct code from customers

Of course, you can not use the pivot function, equivalent to the following statements, but the code is longer, 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) grapes from demo where name= ' grapes '), (select sum (nums) Mango from demo where name= ' Mango ');       

Unpivot Row to Column

As the name implies, convert multiple columns into 1 columns.
Case: Now there is a fruit table that records the sales volume for 4 quarters, and now it is time to show the sales of each fruit quarterly with multiple rows of data.

Creating Tables and data

CREATE TABLE Fruit (ID int,name varchar (), Q1 int, Q2 int, Q3 int, Q4 int); INSERT into Fruit values (1, ' Apple ', 1000,2000,3300 INSERT into Fruit values (2, ' orange ', 3000,3000,3200,1500), insert into Fruit values (3, ' banana ', 2500,3500,2200,2500); INSERT into Fruit values (4, ' grapes ', 1500,2500,1200,3500); select * FROM Fruit

Query for a row career

Select ID, name, Jidu, Xiaoshou from Fruit unpivot (Xiaoshou-Jidu in (Q1, Q2, Q3, Q4))

Note: Unpivot does not have an aggregate function, and the Xiaoshou and Jidu fields are also temporary variables

The same effect can be achieved without using UNPIVOT, except that the SQL statement will be very long and the speed of execution 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, (SE Lect Q2 from Fruit where id=f.id) Xiaoshou from fruit funionselect ID, name, ' Q3 ' Jidu, (select Q3 from fruit where id=f.i d) Xiaoshou from Fruit funionselect ID, name, ' Q4 ' Jidu, (select Q4 from Fruit where id=f.id) Xiaoshou from Fruit F

XML type

In the pivot column switch example above, you already know what types of queries need to be queried, including in (), assuming that if you don't know what the values are, how do you build the query?

Another clause in the pivot operation XML can be used to resolve this problem. This clause allows you to create an output that performs a pivot operation in XML format, where you can specify a special clause of any rather than a literal value

Examples are as follows:

SELECT * FROM (   select name, nums as "Purchase Frequency" from   demo t)                              pivot XML (   sum (nums) for name in ( Any))

As you can see, column name_xml is XMLTYPE, where the root element is <PivotSet>. Each value is represented in the form of a name-value element pair. You can use the output from any XML parser to generate more useful output.

Conclusion

Pivot adds a very important and useful function to the SQL language. You can use the PIVOT function to create a crosstab report for any relational table without having to write confusing, non-intuitive code that contains a large number of decode functions. Similarly, you can use the Unpivot action to transform any crosstab report and store it in the form of a regular relational table. Pivot can produce output in regular text or XML format. In the case of XML-formatted output, you do not have to specify a range of domains that the pivot operation needs to search.

Summary of SQL statements for Oracle row-to-column, column-changing lines (GO)