Summary of SQL statements for Oracle row-to-column, column-changing

Reference article: http://blog.csdn.net/tianlesoftware/article/details/4704858Multi-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 multi-column is actually the problem of splitting the string, you can use the SUBSTR, InStr, Regexp_substr function modeString to multiple linesUse UNION ALLfunctions and other methods
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, followed by an example to see how this magical function can be applied Preparing 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? Of course, look at me. If you apply wm_concat to make this requirement simple,Let's say 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 Careertest 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

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 columnAs the name implies, convert multiple columns into 1 columns.
Case:Now there is a fruit table that records the number of sales 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

column to row query

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 Typein 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?

Summary of SQL statements for Oracle row-to-column, column-changing