Oracle Database Row_number () over Statistics Top 15 enterprises, WM_CONCAT (case and then) row to column

1.

Basic usage of the row_number () over functionsyntax: Row_number () Over (PARTITION by column ORDER by column)
For example: Row_number () Over (PARTITION by COL1 Order by COL2) is based on COL1 grouping, sorted by COL2 within the grouping, The value computed by this function represents the sequential number of each set of internally ordered (consecutive unique within the group)
such as: different product categories and units of measurement, all enterprises to query the rankings.

SELECT Product_type Product category,       prickle unit of measure,       production_name Enterprise name,       row_number () over (partition by Product_type , prickle ORDER by sum (pur.purchase_num) desc)   ranking from  t_purchase_info pur group  by Production_name, Product_type, Prickle

Reference: http://www.cnblogs.com/fxgachiever/archive/2010/09/15/1826792.html

2. Column Change Wm_concat (case and then)find out the different product categories under different units of measurement, statistics of the top 15 enterprises of the declaration amount

The results are:

Select Product_type Product category, Prickle Unit of measure, Wm_concat (case if r = 1 then PR                 Oduction_name end) First place, Wm_concat (case is R = 1 then value1                 End) Declaration Amount, Wm_concat (case when r = 2 then Production_name End) second place, Wm_concat (case when r = 2 then value1 end) Shen       Wm_concat (case when r = 3 then Production_name end) third place, Wm_concat (case if r = 3 then value1 end) The amount declared, Wm_concat (c                   ASE when r = 4 then Production_name end) fourth place, Wm_concat (case                   When r = 4 then value1 end) Declaration Amount, Wm_concat (case        When r = 5 Then            Production_name end) fifth place, Wm_concat (case when r = 5 Then Value1 end) Declaration Amount, Wm_concat (case when r = 6 then Productio                 N_name end) Sixth place, Wm_concat (case if r = 6 then value1                 End) Declaration Amount, Wm_concat (case when r = 7 then Production_name       End) seventh place, Wm_concat (case if r = 7 then value1 end) Declaration amount, Wm_concat (case if r = 8 then Production_name end) eighth place, wm_                   Concat (case if r = 8 then value1 end) The amount of the claim, Wm_concat (case                   When r = 9 then Production_name end) Nineth Place, Wm_concat (case When r = 9 Then                    Value1 end) Declaration Amount, Wm_concat (case when r = Ten Then                    Production_name end) Tenth place, Wm_concat (case when r = Ten Then Value1 end) Declaration Amount, Wm_concat (case when r = one and then Production_na                 Me end) 11th place, Wm_concat (case when r = one and then value1                 End) Declaration Amount, Wm_concat (case when r = Production_name       End) 12th place, Wm_concat (case when r = value1 end) Declaration amount,       Wm_concat (case when r = Production_name end) 13th place,                    Wm_concat (case when r = value1 end) Declaration Amount, Wm_concat (case WhenR = Production_name End) 14th place, Wm_concat (case when r =                    Value1 end) Declaration Amount, Wm_concat (case when r =                    Production_name end) 15th place, Wm_concat (case when r = Value1 end) filings from (SELECT R, Production_name, Product_type, Prickle, value1 from (SE Lect row_number () over (partition by Product_type, prickle order by sum (pur.purchase_num) desc) R, PU R.production_name, Pur.product_type, Prickle, To_char (ro und (sum (Pur.purchase_num), 2), ' 9999999999999999999.99 ') value1 from T_PURC                   Hase_info pur, t_sgproject_info pro WHERE 1 = 1 and pro.id = pur.project_id and (pro.gclb = ' roomHouse building Works ' OR PRO.GCLB is NULL) and pro.status! = 9 and Product_regdate >=                       To_date (' 2014-01-01 ', ' yyyy-mm-dd hh24:mi:ss ') and Product_regdate <=  To_date (' 2014-12-31 23:59:59 ', ' yyyy-mm-dd hh24:mi:ss ') and Sgproject_type = 1 GROUP by Production_name, Product_type, prickle) WHERE R <= ORDER by Product_type, Prickle, R) GROUP by prod Uct_type, Prickle

Oracle Database Row_number () over Statistics Top 15 enterprises, WM_CONCAT (case and then) row to column