oracle多行转单行多列

oracle多行转单行多列（列数需要固定）

需求：从A转成B
A：

B：

需要用到以下函数：wm_concat（多行转成一列）、case when then else end、regexp_substr

sql如下：

	SELECT material_code,
                 store_id,
                 case
                   WHEN REGEXP_SUBSTR(wm_concat(shipping_space),'[^,]+',1,1) = '好件位' THEN
                    REGEXP_SUBSTR(wm_concat(place_note), '[^,]+', 1, 1)
                   WHEN REGEXP_SUBSTR(wm_concat(shipping_space),'[^,]+',1,2) = '好件位' THEN
                    REGEXP_SUBSTR(wm_concat(place_note), '[^,]+', 1, 2)
                   WHEN REGEXP_SUBSTR(wm_concat(shipping_space),
                                      '[^,]+',1,3) = '好件位' THEN
                    REGEXP_SUBSTR(wm_concat(place_note), '[^,]+', 1, 3)
                   else
                    ''
                 END good,
                 case
                   WHEN REGEXP_SUBSTR(wm_concat(shipping_space),'[^,]+',1,1) = '坏件位' THEN
                    REGEXP_SUBSTR(wm_concat(place_note), '[^,]+', 1, 1)
                   WHEN REGEXP_SUBSTR(wm_concat(shipping_space),'[^,]+',1,2) = '坏件位' THEN
                    REGEXP_SUBSTR(wm_concat(place_note), '[^,]+', 1, 2)
                   WHEN REGEXP_SUBSTR(wm_concat(shipping_space),'[^,]+',1,3) = '坏件位' THEN
                    REGEXP_SUBSTR(wm_concat(place_note), '[^,]+', 1, 3)
                   else
                    ''
                 END bad,
                 case
                   WHEN REGEXP_SUBSTR(wm_concat(shipping_space),'[^,]+',1,1) = '待修位' THEN
                    REGEXP_SUBSTR(wm_concat(place_note), '[^,]+', 1, 1)
                   WHEN REGEXP_SUBSTR(wm_concat(shipping_space),'[^,]+',1,2) = '待修位' THEN
                    REGEXP_SUBSTR(wm_concat(place_note), '[^,]+', 1, 2)
                   WHEN REGEXP_SUBSTR(wm_concat(shipping_space),'[^,]+',1,3) = '待修位' THEN
                    REGEXP_SUBSTR(wm_concat(place_note), '[^,]+', 1, 3)
                   else
                    ''
                 END repair
            from t_spt_material_place t 
            ---若要查全部数据则去掉
            where t.material_code='103010001'
           group by material_code, store_id;