Dynamic row-to-column transfer through sql

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!