Oracle SQL Subtle SQL statement (i)

DROP TABLET_CHANGE_LC;
CREATE TABLE t_change_lc (Card_code VARCHAR2 (3), Q number, bal number);

INSERT INTOT_CHANGE_LC
SELECT ' 001 ' Card_code, ROWNUM Q, Trunc (dbms_random. VALUE *) Bal fromdualconnect by ROWNUM <= 4
UNION
SELECT ' 002 ' Card_code, ROWNUM Q, Trunc (dbms_random. VALUE * () Bal fromdualconnect by ROWNUM <= 4;

SELECT * FROMT_CHANGE_LC;

SELECT A.card_code, SUM (Decode (A.Q, 1, a.bal, 0)) Q1,
SUM (Decode (A.Q, 2, A.bal, 0)) Q2,
SUM (Decode (A.Q, 3, A.bal, 0)) Q3,
SUM (Decode (A.Q, 4, A.bal, 0)) Q4
From T_CHANGE_LC A
GROUP by A.card_code
ORDER by 1;

--row and column conversions
DROP TABLE t_change_cl;
CREATE TABLE T_CHANGE_CL as
SELECT A.card_code,
SUM (Decode (A.Q, 1, a.bal, 0)) Q1,
SUM (Decode (A.Q, 2, A.bal, 0)) Q2,
SUM (Decode (A.Q, 3, A.bal, 0)) Q3,
SUM (Decode (A.Q, 4, A.bal, 0)) Q4
From T_CHANGE_LC A
GROUP by A.card_code
ORDER by 1;

SELECT * FROMT_CHANGE_CL;

SELECT T.card_code,
T.rn Q,
Decode (T.RN, 1, t.q1, 2, T.Q2, 3, T.Q3, 4, T.q4) Bal
From (SELECT a.*, B.rn
From T_change_cl A,
(SELECT ROWNUM rn from dual CONNECT by ROWNUM <= 4) b) t
ORDER by 1, 2;

--row and column transformation rows merge
DROP TABLE T_change_lc_comma;
CREATE TABLE T_change_lc_comma as SELECT card_code, ' Quarter_ ' | | Q as QFROMT_CHANGE_LC;

SELECT * from T_change_lc_comma;

SELECT t1.card_code,substr (MAX (Sys_connect_by_path (t1.q, '; ')), 2) Q
From (SELECT A.card_code,
A.Q,
Row_number () over (PARTITION by A.card_code ORDER by A.Q) RN
From T_change_lc_comma a) T1
START with T1.RN = 1
CONNECT by T1.card_code = PRIOR T1.card_code

and t1.rn-1 = PRIOR T1.rn
GROUP by T1.card_code;

--row and column conversion row Division
DROP TABLE T_change_cl_comma;
CREATE TABLE T_change_cl_comma as
SELECT T1.card_code, substr (MAX (Sys_connect_by_path (t1.q, '; ')), 2) Q
From (SELECT A.card_code,
A.Q,
Row_number () over (PARTITION by A.card_code ORDER by A.Q) RN
From T_change_lc_comma a) T1
START with T1.RN = 1
CONNECT by T1.card_code = PRIOR T1.card_code
and t1.rn-1 = PRIOR T1.rn
GROUP by T1.card_code;

SELECT * FROMT_CHANGE_CL_COMMA;

SELECT T.card_code,
SUBSTR (T.Q,
InStr ('; ' | | t.q, '; ', 1, RN),
InStr (T.q | | '; ', '; ', 1, RN)-InStr ('; ' | | t.q, '; ', 1, RN)) Q
From (SELECT A.card_code, A.Q, B.rn
From T_change_cl_comma A,
(SELECT ROWNUM rn from dual CONNECT by ROWNUM <=) b
WHERE InStr ('; ' | | a.q, '; ', 1, RN) > 0) t
ORDER by 1, 2;

--Implement a record based on conditional multiple table insertions
DROP TABLE t_ia_src;
CREATE TABLE t_ia_src as SELECT ' a ' | | ROWNUMC1, ' B ' | | ROWNUM C2 from dual Connectby rownum<=5;
DROP TABLE t_ia_dest_1;
CREATE TABLE t_ia_dest_1 (Flag VARCHAR2 (Ten), CVARCHAR2 (10));
DROP TABLE t_ia_dest_2;
CREATE TABLE t_ia_dest_2 (Flag VARCHAR2 (Ten), CVARCHAR2 (10));
DROP TABLE T_ia_dest_3;
CREATE TABLE t_ia_dest_3 (Flag VARCHAR2 (Ten), CVARCHAR2 (10));

SELECT * from T_IA_SRC;
SELECT * from T_ia_dest_1;
SELECT * from T_ia_dest_2;
SELECT * from T_ia_dest_3;

INSERT All
When (C1 in (' A1 ', ' A3 ')) Then
Into T_ia_dest_1 (flag,c) VALUES (FLAG1,C2)
When (C1 in (' A2 ', ' A4 ')) then
Into T_ia_dest_2 (flag,c) VALUES (FLAG2,C2)
ELSE
Into T_ia_dest_3 (flag,c) VALUES (flag1| | flag2,c1| | C2)
SELECT c1,c2, ' F1 ' Flag1, ' F2 ' Flag2 from T_IA_SRC;

Oracle SQL Subtle SQL statement (i)