Oracle row to column (dynamic row to indeterminate column)

/* The quantity required for the item requires the warehouse quantity of the existing quantity of the warehouse volume batch
A1 2 C1 C1 20 123
A1 2 C1 C2 30 111
A1 2 C1 C2 20 222
A1 2 C1 C3 10 211
A2 3 C4 C1 40 321
A2 3 C4 C4 50 222
A2 3 C4 C4 60 333
A2 3 C4 C5 70 223
I need to convert the above query results to the following.
Required quantity of material required warehouse C1 C2 C3 C4 C5
A1 2 C1 20 50 10 0 0
A2 3 C4 40 0 0 110 70
*/
---------------------------------------------------------------Building a table
----------------determine if a table exists
declare num number;
Begin
Select COUNT (1) into Num from user_tables where table_name= ' TEST ';
If Num>0 Then
Execute immediate ' drop table TEST ';
End If;
End
----------------Building a table
CREATE TABLE TEST (
WL VARCHAR2 (10),
XYSL INTEGER,
Xyck VARCHAR2 (10),
Xclck VARCHAR2 (10),
XCLCKSL INTEGER,
PC INTEGER
);
----------------the first part of the test data
INSERT into TEST VALUES (' A1 ', 2, ' C1 ', ' C1 ', 20, 123);
INSERT into TEST VALUES (' A1 ', 2, ' C1 ', ' C2 ', 30, 111);
INSERT into TEST VALUES (' A1 ', 2, ' C1 ', ' C2 ', 20, 222);
INSERT into TEST VALUES (' A1 ', 2, ' C1 ', ' C3 ', 10, 211);
INSERT into TEST VALUES (' A2 ', 3, ' C4 ', ' C1 ', 40, 321);
INSERT into TEST VALUES (' A2 ', 3, ' C4 ', ' C4 ', 50, 222);
INSERT into TEST VALUES (' A2 ', 3, ' C4 ', ' C4 ', 60, 333);
INSERT into TEST VALUES (' A2 ', 3, ' C4 ', ' C5 ', 70, 223);
COMMIT;
--select * from Test;
Stored procedure---------------------------------------------------------------row to column
CREATE OR REPLACE PROCEDURE p_test is
V_sql VARCHAR2 (2000);
CURSOR cursor_1 is a SELECT DISTINCT t.xclck from TEST T ORDER by Xclck;

BEGIN
V_sql: = ' SELECT Wl,xysl,xyck ';

For V_xclck in cursor_1
LOOP
V_sql: = V_sql | | ', ' | | ' SUM (DECODE (Xclck, "' | | V_xclck. Xclck | |
', xclcksl,0) as ' | | V_xclck. Xclck;
END LOOP;

V_sql: = V_sql | | ' From TEST GROUP by Wl,xysl,xyck ORDER by Wl,xysl,xyck ';
--dbms_output. Put_Line (V_sql);
V_sql: = ' CREATE OR REPLACE VIEW RESULT as ' | | V_sql;
--dbms_output. Put_Line (V_sql);
EXECUTE IMMEDIATE V_sql;
END;
----------------------------------------------------------------Results
----------------execute a stored procedure, generate a view
BEGIN
P_test;
END;
----------------Results
SELECT * from RESULT T;
WL XYSL xyck C1 C2 C3 C4 C5
---------- --------------------------------------- ---------- ---------- ---------- ---------- ---------- ----------
A1 2 C1 20 50 10 0 0
A2 3 C4 40 0 0 110 70

----------------the second part of the test data
INSERT into TEST VALUES (' A1 ', 2, ' C1 ', ' C6 ', 20, 124);
INSERT into TEST VALUES (' A2 ', 2, ' C1 ', ' C7 ', 30, 121);
INSERT into TEST VALUES (' A3 ', 2, ' C1 ', ' C8 ', 20, 322);
COMMIT;
----------------report stored procedures, generating views
BEGIN
P_test;
END;
----------------Results
SELECT * from RESULT T;
WL XYSL xyck C1 C2 C3 C4 C5 C6 C7 C8
----- ----- -------- ------- -------- ---------- ---------- ---------- ---------- ---------- ----------
A1 2 C1 20 50 10 0 0 20 0 0
A2 2 C1 0 0 0 0 0 0 30 0
A2 3 C4 40 0 0 110 70 0 0 0
A3 2 C1 0 0 0 0 0 0 0 20
---------------Delete Entities
DROP VIEW RESULT;
DROP PROCEDURE p_test;
DROP TABLE TEST;

Http://www.cnblogs.com/QQParadise/articles/1712093.html

Oracle row to column (dynamic row to indeterminate column)