Oracle uses the transform function for dynamic row-to-column conversion of large batches of data (Code tutorial), oracle.pdf

Oracle uses the transform function for dynamic row-to-column conversion of large batches of data (Code tutorial), oracle.pdf

The company's business requirements require dynamic row-to-Column Analysis of a batch of data in large batches, and subqueries cannot be used in the exist function in, there is a way to achieve this through dynamic SQL concatenation. After finding a method on the Internet for a long time, I did not find a specific method, so I tried to implement it myself. The specific method is as follows.

Because dynamic row-to-column conversion is required, you must first use the wm_concat (distinct () function to identify the column name separately, and then use dbms_lob.substr () to convert the clob field to a string. Then the column names are dynamically spliced for query. You can create a view to store the query results and directly query the expected table. The specific code is as follows:

PROCEDURE P_FXJG AS v_LM VARCHAR2 (2000); vs_ SQL varchar2 (6000); vs_sql1 varchar2 (6000); BEGIN vs_sql1: = 'insert INTO LS_TEMP select partition (wm_concat (distinct (sf ))) from B _CLB a'; execute immediate vs_sql1; select sfbm into V_LM FROM LS_TEMP; vs_ SQL: = 'create or replace view V_FXJG as select * from (select. YH,. MC,. LX "BZ",. SF,. JG from B _CLB a) terminate (sum (JG) for SF in ('| V_LM |') '; execute immediate vs_ SQL; END;