报表SQL:casewhen-decode 行转列

基本信息:
SELECT DISTINCT
     nvl(sp."PRD_PLAN_ID",' ') AS 作业计划号,
     nvl(spd."MAT_ID",' ') AS 原料号,
     nvl(spd."UNIT_NAME",' ') AS 加工线,
     nvl(sp."CONSIGN_NAME",' ') AS 加工客户,
     nvl(sco."ORDER_NUM",' ') AS 销售合同号,
     nvl(xsm."ORDER_NUM",' ') AS 加工单号,
     nvl(xsm."SALE_REMARK",' ') AS 备注,
     nvl(xsm."RESO_PERIOD",' ') AS 特殊要求
FROM
     "SC_PLAN" sp INNER JOIN "SC_PLAN_DETAIL" spd ON sp."PRD_PLAN_ID" = spd."PRD_PLAN_ID"
     INNER JOIN "SC_ORDER_PLAN" sco ON sp."PRD_PLAN_ID" = sco."PRD_PLAN_ID"
     INNER JOIN "XS_ORDER_MAIN" xsm ON sco."ORDER_NUM" = xsm."ORDER_NUM"
WHERE
     sp.PRD_PLAN_ID = $P{prdPlanId}


--原料信息
select rownum as 序号,nvl(cc.TYPE_CODE,' ') as 物资来源,
  nvl(cc.MATERIAL_NAME,' ') as 产品名称,
  nvl(cc.MAT_SIZE,' ') as 规格,
  nvl(cc.TEXTURE,' ') as 材质,
  nvl(decode(substr(to_char(cc.GROSS_WEIGHT),1,1),'.','0'||to_char(cc.GROSS_WEIGHT),to_char(cc.GROSS_WEIGHT)),' ') as 重量,
  nvl(cc.MANUFACTURER,' ') as 产地,
  nvl(m."KUN_BANG_NUM",' ') as 捆包号,
  nvl(cc.LOCATION_NAME,' ') as 货位
  from CC_INVENTORY cc,MACHINING_ORDER_RAW_MAT m
  where cc.MATERIAL_NO in(
    select spd.MAT_ID
    from SC_PLAN_DETAIL spd
    where spd.PRD_PLAN_ID=$P{scPlanId})
  and m.material_no=cc."MOTHER_COIL"


--开平尺寸
select nvl(to_char(substr(wm_concat(kpSize),1,1000)),' ') kpSize from(
SELECT
     sp."PRD_PLAN_ID" AS "作业计划号",case when isnumer(sco."PRD_ORD_NUM")=1 then  '('||rownum||')【'||sco.PRD_LENGTH/1000||'米'||nvl(sco."PRD_ORD_NUM",0)||'张】'
     else '('||rownum||')【'||sco.PRD_LENGTH/1000||'米'||nvl(sco."PRD_ORD_NUM",0)||'】' end kpSize
FROM
     SC_PLAN sp INNER JOIN SC_ORDER sco ON sp.PRD_PLAN_ID = sco.PRD_PLAN_ID
WHERE
     sp.PRD_PLAN_ID = $P{scPlanId}) t