Shulou(Shulou.com)06/01 Report--

In the previous chapter, we talked about fixed row transfer. In this chapter, we will show how to dynamically implement row transfer. Because sometimes there are thousands of values in a row-specific column, it is impossible to use a fixed row-to-column method, otherwise you will certainly collapse. All right, let's cut the crap and let's go!

A common table, tmp_test, contains the following:

Implementation code:

Create or replace procedure p_test is

V_sql varchar2 (2000)

Cursor cursor_1 is

Select distinct subject from tmp_test order by subject

Begin

V_sql: = 'select username'

For v_subject in cursor_1 loop

V_sql: = v_sql | |','| 'sum (decode (subject,''' | | v_subject.subject | |'', source)) as'| | v_subject.subject

Dbms_output.put_line (v_sql)

End loop

V_sql: = vql | | 'from tmp_test group by username'

Dbms_output.put_line (v_sql)

V_sql: = 'create or replace view test_result as' | | v_sql

Execute immediate v_sql

End

The stored procedure has been written and started to be called and saved:

Begin

P_test

End

Query View test_result:

Code implementation:

Select * from test_result

The data are as follows:

At this point, dynamic row rotation has been realized! I hope it will be helpful to the little friends!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.