Oracle row-column conversion application

In Oracle, an application of row and column Conversion

The table structure is omitted. The SQL statement is as follows:

select z.* from (select row_number() over(order by bs_cnt desc) rn, inn2.* from (select distinct sum(bv_sum) over(partition by department_code)+sum(sv_sum) over(partition by department_code) bsv_sum, sum(bv_sum) over(partition by department_code)-sum(sv_sum) over(partition by department_code) net_amount, sum(bv_sum) over(partition by department_code) bv_sum, sum(sv_sum) over(partition by department_code) sv_sum, sum(bs_cnt) over(partition by department_code) bs_cnt, sum(b_cnt) over(partition by department_code) b_cnt, sum(s_cnt) over(partition by department_code) s_cnt, department_code,department_name from (select nvl(sum(buy_value),0) bv_sum, nvl(sum(sale_value),0) sv_sum, count(trade_type) bs_cnt, decode(trade_type,'B',count(trade_type),0) b_cnt,  decode(trade_type,'S',count(trade_type),0) s_cnt, department_code,department_name from (select distinct t.department_code,t.department_name,t.buy_value,t.sale_value,t.tradedate,t.trade_type  from mv_stk_trans_info t where to_char(t.tradedate,'yyyy-mm-dd')<=to_char(sysdate,'yyyy-mm-dd') and to_char(t.tradedate,'yyyy-mm-dd')>=to_char(sysdate-30,'yyyy-mm-dd') and t.sec_code='300231') inn group by department_code,department_name,trade_type) inn1 order by bs_cnt desc) inn2 ) z where z.rn<=10 

Figure 1-4 shows the conversion process. The main function is to use trade_type to view the total transaction amount, total sales amount, and number of purchases and purchases of the most frequently-occurring securities department in a certain period of time, the focus is on the statistical aggregation of buy_value, sale_value, and trade_type to generate new columns bs_cnt, B _cnt, s_cnt, bv_sum, sv_sum, and bsv_sum. The corresponding SQL statements are gradually generated from the inside out.

The last graph is the data obtained in reverse order.

1.

2.

3.

4.