Oracle row to column, column change

Multi-line Turn string
This is relatively simple, with | | Or the CONCAT function can be implemented
SQL Code
Select Concat (id,username) str from App_user
Select 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
SQL Code
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: Row to column, comma separated by default
SQL Code
Select Wm_concat (name) name from test;

Effect 2: Replace the comma in the result with "|"
SQL Code
Select Replace (wm_concat (name), ', ', ' | ') from test;

Effect 3: Group by ID merge name

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

The SQL statement is equivalent to the following SQL statement:
SQL Code
--------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 RN
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 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;
--------Scope of application: 10g and later version (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 ()) 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;
--------Scope of application: 8i,9i,10g and later (MAX + DECODE)
Select T.id 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;

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
SQL Code
/** 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
SQL Code
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.
SQL Code
CREATE TABLE demo (ID int,name varchar (), nums int); ----Create a 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, ' grapes ', 3500);
Insert 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)
SQL Code
Select Name, sum (nums) Nums from demo GROUP by name

Row-to-column query
SQL Code
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
SQL Code
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 ');
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
SQL Code
CREATE TABLE Fruit (ID int,name varchar (), Q1 int, Q2 int, Q3 int, Q4 int);
INSERT into Fruit values (1, ' Apple ', 1000,2000,3300,5000);
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
SQL Code
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.
SQL Code
Select ID, name, ' Q1 ' Jidu, (select Q1 from fruit where id=f.id) Xiaoshou from Fruit f
Union
Select ID, name, ' Q2 ' Jidu, (select Q2 from fruit where id=f.id) Xiaoshou from Fruit f
Union
Select ID, name, ' Q3 ' Jidu, (select Q3 from fruit where id=f.id) Xiaoshou from Fruit f
Union
Select ID, name, ' Q4 ' Jidu, (select Q4 from fruit where id=f.id) Xiaoshou from Fruit f

Oracle row to column, column change