Row column, column career change

One: Row to column


Mode: N row to a column of this experiment n take 3
1. Table Building
Sql> CREATE TABLE grade (ID int,name varchar2 (a), subject varchar2 (a), Grade number (3));
2. Insert experimental data
Insert into grade values (1, ' Andy ', ' Yuwen ', 11);
Insert into grade values (2, ' Andy ', ' Math ', 22);
Insert into grade values (3, ' Andy ', ' 中文版 ', 33);
Insert into grade values (4, ' Zhang ', ' 中文版 ', 44);
Insert into grade values (5, ' Zhang ', ' Math ', 55);
Insert into grade values (6, ' Zhang ', ' Yuwen ', 66);
Insert into grade values (7, ' Tao ', ' Yuwen ', 77);
Insert into grade values (8, ' Tao ', ' Math ', 88);
Commit
3. Query
Sql> select * from grade;


ID NAME SUBJECT GRADE
---------- ---------- ---------- ----------
1 Andy Yuwen 11
2 Andy Math 22
3 Andy 中文版 33
4 Zhang 中文版 44
5 Zhang Math 55
6 Zhang Yuwen 66
7 Tao Yuwen 77
8 Tao Math 88
8 rows selected.
4. Error demonstration, missing sum.
Sql>
Select Name,
Decode (subject, ' Yuwen ', grade,null) "Language",
Decode (subject, ' math ', grade,null) "math",
Decode (subject, ' 中文版 ', grade,null) "中文版"
from grade;


NAME Language Math 中文版
---------- ---------- ---------- ----------
Andy 11
Andy 22
Andy 33
Zhang 44
Zhang 55
Zhang 66
Tao 77
Tao 88


4. Conversion. Correct demonstration
Method One:
Sql>
Select Name,
SUM (decode (subject, ' Yuwen ', grade,0)) "Yuwen",
SUM (decode (subject, ' math ', grade,0)) "Math",
SUM (decode (subject, ' 中文版 ', grade,0)) "中文版"
From grade
Group by name;


NAME Yuwen Math 中文版
---------- ---------- ---------- ----------
Zhang 66 55 44
Andy 11 22 33
Tao 77 88 0
Method Two:
SELECT name,
SUM (case when subject= ' Yuwen ' THEN grade ELSE 0) as "Yuwen",
SUM (case when subject= ' math ' THEN grade ELSE 0) as "math",
SUM (case when subject= ' 中文版 ' THEN grade ELSE 0 end) as "中文版"
From grade
Group by name;


Second, the column to change careers


Mode: A column of n rows, this experiment n take 3 (note: The same type of data to determine the value of n, such as: Yuwen,math,english so take 3)
1. Table Building
CREATE TABLE score (ID int,name varchar2 (), Yuwen Number (3), Math number (3), 中文版 number (3));
2. Inserting data
INSERT into score values (1, ' Zhang ', 11,22,33);
INSERT INTO score values (2, ' Andy ', 44,55,66);
Insert INTO score (Id,name,yuwen) VALUES (3, ' Tao ', 77);
Commit
3. Query
Sql> select * from score;


ID NAME Yuwen MATH 中文版
---------- ---------- ---------- ---------- ----------
1 Zhang 11 22 33
2 Andy 44 55 66
3 Tao 77
4. Conversion
SELECT name, ' Yuwen ' Subject,yuwen grade from score
UNION All
SELECT name, ' Math ' Subject,math grade from score
UNION All
SELECT name, ' 中文版 ' Subject,english grade from score
Order BY name;


NAME SUBJECT GRADE
---------- ------- ----------
Andy Math 55
Andy 中文版 66
Andy Yuwen 44
Tao 中文版
Tao Math
Tao Yuwen 77
Zhang Math 22
Zhang 中文版 33
Zhang Yuwen 11
9 rows selected.
Note: The quoted value is a string that is not quoted as a column
Mode: n columns into one line
1. Table Building
CREATE TABLE Test (dname varchar2), ename varchar2 (10));
2. Inserting data
INSERT into test values (' Guowang ', ' Andy ');
INSERT into test values (' Guowang ', ' Zhang ');
INSERT into test values (' Guowang ', ' Chong ');
INSERT into test values (' Nvwang ', ' Tao ');
INSERT into test values (' Nvwang ', ' Ye ');
INSERT into test values (' Happy ', ' Leaf ');
Commit
3. Query
Sql> select * from test;


Dname ename
---------- ----------
Guowang Andy
Guowang Zhang
Guowang Chong
Nvwang Tao
Nvwang Ye
Happy Leaf
6 rows selected.
Target Conversion Mode:
Dname NAME
---------- ----------------
Guowang Andy,zhang,chong
Nvwang Tao,ye
Happy Leaf
4. Conversion
Method One:
A simple way to pass a function, with Oracle versioning as an alternative.
Sql> col name for A40
Sql> Select Dname,wmsys. Wm_concat (ename) NAME from test group by Dname;


Dname NAME
---------- ----------------------------------------
Guowang Andy,chong,zhang
Nvwang Tao,ye
Happy Leaf
Method Two:
Constructs a level, utilizes the hierarchical query processing
4.2.1--Fictitious employee number
Sql> SELECT dname, ename, Row_number () over (order by dname) RN from test;


Dname ename RN
---------- ---------- ----------
Guowang Andy 1
Guowang Zhang 2
Guowang Chong 3
Nvwang Tao 4
Nvwang Ye 5
Happy Leaf 6
4.2.2--leader Number
SELECT dname, ename, RN, lead (RN) over (PARTITION by dname ORDER by RN) RN1
From (SELECT dname, ename, Row_number () over (order by dname) RN from Test);
Dname ename RN RN1
---------- ---------- ---------- ----------
Guowang Andy 1 2
Guowang Zhang 2 3
Guowang Chong 3
Nvwang Tao 4 5
Nvwang Ye 5
Happy Leaf 6
4.2.3 Conversion
Col name for A40;
SELECT dname, SUBSTR (MAX (Sys_connect_by_path (ename, ', '), 2) NAME
From (
SELECT dname, ename, RN, lead (RN) over (PARTITION by dname ORDER by RN) RN1
From (SELECT dname, ename, Row_number () over (order by ename) RN
From Test)
)
START with RN1 is NULL
CONNECT by rn1 = PRIOR RN
GROUP by Dname;


Dname NAME
---------- -----------------------------
Happy Leaf
Nvwang Ye,tao
Guowang Zhang,chong,andy