行转列 一条工程信息多个产品类别为一列显示

/*统计正在备案工程信息，
并统计建筑钢材、混凝土、防水卷材、塑料管材、建筑外窗、预拌砂浆六类产品类别对应的备案批次与总量，工程面积。
*/

--select count(id) beiancishu,sum(r.purchase_num),project_id from bemms.t_purchase_info r group by product_type ,project_id 
select build_licence 施工许可证号,
       pro_area 工程面积,
       to_char(licence_date, 'yyyy-MM-dd') 发证日期,
       project_name 工程名称,
       contractor_name 施工单位,
       bemms.fun_get_quality_supervision(quality_supervision) 质量监督机构,
       to_char(htkgrq, 'yyyy-MM-dd') 合同开工日期,
       to_char(htjgrq, 'yyyy-MM-dd') 合同竣工日期,
       to_char(operate_date, 'yyyy-MM-dd') 最后备案时间,
       wm_concat(case
                   when product_type = 1 then
                    ba_count
                 end) 建筑钢材备案次数,
       wm_concat(case
                   when product_type = 1 then
                    ba_sum || '吨'
                 end) 建筑钢材备案总量,
       wm_concat(case
                   when product_type = 2 then
                    ba_count
                 end) 预拌混凝土备案次数,
       wm_concat(case
                   when product_type = 2 then
                    ba_sum || '立方米'
                 end) 预拌混凝土备案总量,
       wm_concat(case
                   when product_type = 3 then
                    ba_count
                 end) 防水卷材备案次数,
       wm_concat(case
                   when product_type = 3 then
                    ba_sum || '平方米'
                 end) 防水卷材备案总量,
       wm_concat(case
                   when product_type = 4 then
                    ba_count
                 end) 防水涂料备案次数,
       wm_concat(case
                   when product_type = 4 then
                    ba_sum || '千克'
                 end) 防水涂料备案总量,
       wm_concat(case
                   when product_type = 5 then
                    ba_count
                 end) 建筑外窗备案次数,
       wm_concat(case
                   when product_type = 5 then
                    ba_sum || '平方米'
                 end) 建筑外窗备案总,
       wm_concat(case
                   when product_type = 7 then
                    ba_count
                 end) 塑料管材管件备案次数,
       wm_concat(case
                   when product_type = 7 then
                    ba_sum || '米'
                 end) 塑料管材管件备案总量,
       wm_concat(case
                   when product_type = 9 then
                    ba_count
                 end) 预拌砂浆备案次数,
       wm_concat(case
                   when product_type = 9 then
                    ba_sum || '吨'
                 end) 预拌砂浆备案总量
  from (select t1.build_licence,
               t2.ba_count,
               t2.ba_sum,
               t2.product_type,
               t1.pro_area,
               t1.licence_date,
               t1.project_name,
               t1.contractor_name,
               t1.quality_supervision,
               t1.htkgrq,
               t1.htjgrq,
               t1.id,
               t1.operate_date
          from (select s.build_licence,
                       s.pro_area,
                       s.licence_date,
                       s.project_name,
                       s.contractor_name,
                       s.quality_supervision, --质量监督机构
                       s.htkgrq,
                       s.htjgrq,
                       s.id,
                       CASE
                         WHEN p.last_repdata IS NULL THEN
                          s.operate_date
                         ELSE
                          last_repdata
                       END operate_date --最后备案时间
                
                  from T_SGPROJECT_INFO s
                  left join (SELECT project_id, MAX(operate_date) last_repdata
                              FROM t_purchase_info
                             GROUP BY project_id) p
                    ON s.id = p.project_id
                 where 1 = 1
                   and sgproject_type = 1
                   and status in (20, 30)
                   and symbol = 1
                   AND (s.gclb = '房屋建筑工程' OR s.gclb IS NULL)) t1,
               (select count(id) ba_count,
                       sum(r.purchase_num) ba_sum,
                       project_id,
                       product_type
                  from bemms.t_purchase_info r
                 group by product_type, project_id) t2
         where t1.id = t2.project_id
         order by t1.build_licence, product_type)

 group by build_licence,
          pro_area,
          licence_date,
          project_name,
          contractor_name,
          quality_supervision,
          htkgrq,
          htjgrq,
          operate_date
 order by build_licence;