Row-column conversion _ wmsys.wm_concat

Requirement description

Loc_sys_busi_strategy is a vertical table, which needs to be converted into a horizontal table to facilitate Redis QUEUE processing.

Source data format

Demand format

Notice that the speed,area is the title heading in the requirements format.

Format conversion

Select busirule_id,element_key,element_val from loc_sys_busi_strategy

Title heading conversion

Select busirule_id, 'speed' speed,' area' area from loc_sys_busi_strategy where 1 = 2 unionselect busirule_id, decode (element_key, upper ('speed'), element_val), decode (element_key, upper (' area'), element_val) from loc_sys_busi_strategy

Merge select busirule_id, to_char (wmsys.wm_concat (speed)) speed, to_char (wmsys.wm_concat (area)) area from (select busirule_id, 'speed' speed,' area' area from loc_sys_busi_strategy where 1 = 2 unionselect busirule_id, decode (element_key, upper ('speed'), element_val), decode (element_key, upper (' area')) Element_val) from loc_sys_busi_strategy) group by busirule_id