ORCAL纵表转横表实例

1. 案例解析：

       首先 我现在有两张表：图一为要转为横表的原纵向表，图二为关联的一张从表，根据一个seqid来汇总有几条数据，如图二所示有三条数据，所以图一这张表最后转为横向表之后的数据只能是3条，转为横表后的字段就取决于目前一个seqid有几条数据。

代码实现：

这边的做法就是根据主表的sysid来找到当前的所有数据，这边的fromid就是外键，也就是主表的主键，主表因为没多大关系所以没有体现，然后就是根据seqid做了汇总，有几个sqlid那么就是几条数据，最后通过CASE  WHEN 函数捕获每个字段的值：

SELECT MAX(CASE
             WHEN fieldname = 'FZJD' THEN
              fieldvalue
           END) AS FZJD,
       MAX(CASE
             WHEN fieldname = 'FZWGZB' THEN
              fieldvalue
           END) AS FZWGZB,
       MAX(CASE
             WHEN fieldname = 'CUSTCODE' THEN
              fieldvalue
           END) AS CUSTCODE,
       MAX(CASE
             WHEN fieldname = 'CUSTNAME' THEN
              fieldvalue
           END) AS CUSTNAME,
       MAX(CASE
             WHEN fieldname = 'CUSTORDER' THEN
              fieldvalue
           END) AS CUSTORDER,
       MAX(CASE
             WHEN fieldname = 'CUSTORDERDATE' THEN
              fieldvalue
           END) AS CUSTORDERDATE,
       MAX(CASE
             WHEN fieldname = 'SCH' THEN
              fieldvalue
           END) AS SCH,
       MAX(CASE
             WHEN fieldname = 'JGTYPE' THEN
              fieldvalue
           END) AS JGTYPE,
       MAX(CASE
             WHEN fieldname = 'JGSX' THEN
              fieldvalue
           END) AS JGSX,
       MAX(CASE
             WHEN fieldname = 'PONUM' THEN
              fieldvalue
           END) AS PONUM,
       MAX(CASE
             WHEN fieldname = 'PDDNAME' THEN
              fieldvalue
           END) AS PDDNAME,
       MAX(CASE
             WHEN fieldname = 'PACKAGES' THEN
              fieldvalue
           END) AS PACKAGES,
       MAX(CASE
             WHEN fieldname = 'CUSTMODEL' THEN
              fieldvalue
           END) AS CUSTMODEL,
       MAX(CASE
             WHEN fieldname = 'CH_LOT' THEN
              fieldvalue
           END) AS CH_LOT,
       MAX(CASE
             WHEN fieldname = 'CUST_YZ_LOT' THEN
              fieldvalue
           END) AS CUST_YZ_LOT,
       MAX(CASE
             WHEN fieldname = 'DIENAMWE' THEN
              fieldvalue
           END) AS DIENAMWE,
       MAX(CASE
             WHEN fieldname = 'DIELOT' THEN
              fieldvalue
           END) AS DIELOT,
       MAX(CASE
             WHEN fieldname = 'CH_PDDNAME' THEN
              fieldvalue
           END) AS CH_PDDNAME,
       MAX(CASE
             WHEN fieldname = 'POQTY' THEN
              fieldvalue
           END) AS POQTY,
       MAX(CASE
             WHEN fieldname = 'PO_ZD_DATE' THEN
              fieldvalue
           END) AS PO_ZD_DATE,
       MAX(CASE
             WHEN fieldname = 'GD_TL_DATE' THEN
              fieldvalue
           END) AS GD_TL_DATE,
       MAX(CASE
             WHEN fieldname = 'GD_TL_QTY' THEN
              fieldvalue
           END) AS GD_TL_QTY,
       MAX(CASE
             WHEN fieldname = 'JH_JH_DATE' THEN
              fieldvalue
           END) AS JH_JH_DATE,
       MAX(CASE
             WHEN fieldname = 'KS_SC_DATE' THEN
              fieldvalue
           END) AS KS_SC_DATE,
       MAX(CASE
             WHEN fieldname = 'FZ_JP_DATE' THEN
              fieldvalue
           END) AS FZ_JP_DATE,
       MAX(CASE
             WHEN fieldname = 'SF_MRB_TZKH' THEN
              fieldvalue
           END) AS SF_MRB_TZKH,
       MAX(CASE
             WHEN fieldname = 'SF_DPA' THEN
              fieldvalue
           END) AS SF_DPA,
       MAX(CASE
             WHEN fieldname = 'SF_ORT' THEN
              fieldvalue
           END) AS SF_ORT,
       MAX(CASE
             WHEN fieldname = 'WONUM' THEN
              fieldvalue
           END) AS WONUM,
       MAX(CASE
             WHEN fieldname = 'ZZP_QTY' THEN
              fieldvalue
           END) AS ZZP_QTY,
       MAX(CASE
             WHEN fieldname = 'MP_INT_DATE' THEN
              fieldvalue
           END) AS MP_INT_DATE,
       MAX(CASE
             WHEN fieldname = 'MP_INT_QTY' THEN
              fieldvalue
           END) AS MP_INT_QTY,
       MAX(CASE
             WHEN fieldname = 'MP_OUT_DATE' THEN
              fieldvalue
           END) AS MP_OUT_DATE,
       MAX(CASE
             WHEN fieldname = 'MP_OUT_QTY' THEN
              fieldvalue
           END) AS MP_OUT_QTY,
       MAX(CASE
             WHEN fieldname = 'MP_YIELD' THEN
              fieldvalue
           END) AS MP_YIELD,
       MAX(CASE
             WHEN fieldname = 'MP_BLPFX' THEN
              fieldvalue
           END) AS MP_BLPFX,
       MAX(CASE
             WHEN fieldname = 'HP_INT_DATE' THEN
              fieldvalue
           END) AS HP_INT_DATE,
       MAX(CASE
             WHEN fieldname = 'HP_INT_QTY' THEN
              fieldvalue
           END) AS HP_INT_QTY,
       MAX(CASE
             WHEN fieldname = 'HP_OUT_DATE' THEN
              fieldvalue
           END) AS HP_OUT_DATE,
       MAX(CASE
             WHEN fieldname = 'HP_OUT_QTY' THEN
              fieldvalue
           END) AS HP_OUT_QTY,
       MAX(CASE
             WHEN fieldname = 'HP_YIELD' THEN
              fieldvalue
           END) AS HP_YIELD,
       MAX(CASE
             WHEN fieldname = 'HP_BLPFX' THEN
              fieldvalue
           END) AS HP_BLPFX,
       MAX(CASE
             WHEN fieldname = 'SMT_INT_DATE' THEN
              fieldvalue
           END) AS SMT_INT_DATE,
       MAX(CASE
             WHEN fieldname = 'SMT_INT_QTY' THEN
              fieldvalue
           END) AS SMT_INT_QTY,
       MAX(CASE
             WHEN fieldname = 'SMT_OUT_DATE' THEN
              fieldvalue
           END) AS SMT_OUT_DATE,
       MAX(CASE
             WHEN fieldname = 'SMT_OUT_QTY' THEN
              fieldvalue
           END) AS SMT_OUT_QTY,
       MAX(CASE
             WHEN fieldname = 'SMT_YIELD' THEN
              fieldvalue
           END) AS SMT_YIELD,
       MAX(CASE
             WHEN fieldname = 'SMT_BLPFX' THEN
              fieldvalue
           END) AS SMT_BLPFX,
       MAX(CASE
             WHEN fieldname = 'ZP_INT_DATE' THEN
              fieldvalue
           END) AS ZP_INT_DATE,
       MAX(CASE
             WHEN fieldname = 'ZP_INT_QTY' THEN
              fieldvalue
           END) AS ZP_INT_QTY,
       MAX(CASE
             WHEN fieldname = 'ZP_OUT_DATE' THEN
              fieldvalue
           END) AS ZP_OUT_DATE,
       MAX(CASE
             WHEN fieldname = 'ZP_OUT_QTY' THEN
              fieldvalue
           END) AS ZP_OUT_QTY,
       MAX(CASE
             WHEN fieldname = 'ZP_YIELD' THEN
              fieldvalue
           END) AS ZP_YIELD,
       MAX(CASE
             WHEN fieldname = 'ZP_BLPFX' THEN
              fieldvalue
           END) AS ZP_BLPFX,
       MAX(CASE
             WHEN fieldname = 'DZ_INT_DATE' THEN
              fieldvalue
           END) AS DZ_INT_DATE,
       MAX(CASE
             WHEN fieldname = 'DZ_INT_QTY' THEN
              fieldvalue
           END) AS DZ_INT_QTY,
       MAX(CASE
             WHEN fieldname = 'DZ_OUT_DATE' THEN
              fieldvalue
           END) AS DZ_OUT_DATE,
       MAX(CASE
             WHEN fieldname = 'DZ_OUT_QTY' THEN
              fieldvalue
           END) AS DZ_OUT_QTY,
       MAX(CASE
             WHEN fieldname = 'DZ_YIELD' THEN
              fieldvalue
           END) AS DZ_YIELD,
       MAX(CASE
             WHEN fieldname = 'DZ_BLPFX' THEN
              fieldvalue
           END) AS DZ_BLPFX,
       MAX(CASE
             WHEN fieldname = 'HX_INT_DATE' THEN
              fieldvalue
           END) AS HX_INT_DATE,
       MAX(CASE
             WHEN fieldname = 'HX_INT_QTY' THEN
              fieldvalue
           END) AS HX_INT_QTY,
       MAX(CASE
             WHEN fieldname = 'HX_OUT_DATE' THEN
              fieldvalue
           END) AS HX_OUT_DATE,
       MAX(CASE
             WHEN fieldname = 'HX_OUT_QTY' THEN
              fieldvalue
           END) AS HX_OUT_QTY,
       MAX(CASE
             WHEN fieldname = 'HX_YIELD' THEN
              fieldvalue
           END) AS HX_YIELD,
       MAX(CASE
             WHEN fieldname = 'HX_BLPFX' THEN
              fieldvalue
           END) AS HX_BLPFX,
       MAX(CASE
             WHEN fieldname = 'SF_INT_DATE' THEN
              fieldvalue
           END) AS SF_INT_DATE,
       MAX(CASE
             WHEN fieldname = 'SF_INT_QTY' THEN
              fieldvalue
           END) AS SF_INT_QTY,
       MAX(CASE
             WHEN fieldname = 'SF_OUT_DATE' THEN
              fieldvalue
           END) AS SF_OUT_DATE,
       MAX(CASE
             WHEN fieldname = 'SF_OUT_QTY' THEN
              fieldvalue
           END) AS SF_OUT_QTY,
       MAX(CASE
             WHEN fieldname = 'SF_YIELD' THEN
              fieldvalue
           END) AS SF_YIELD,
       MAX(CASE
             WHEN fieldname = 'SF_BLPFX' THEN
              fieldvalue
           END) AS SF_BLPFX,
       MAX(CASE
             WHEN fieldname = 'DD_INT_DATE' THEN
              fieldvalue
           END) AS DD_INT_DATE,
       MAX(CASE
             WHEN fieldname = 'DD_INT_QTY' THEN
              fieldvalue
           END) AS DD_INT_QTY,
       MAX(CASE
             WHEN fieldname = 'DD_OUT_DATE' THEN
              fieldvalue
           END) AS DD_OUT_DATE,
       MAX(CASE
             WHEN fieldname = 'DD_OUT_QTY' THEN
              fieldvalue
           END) AS DD_OUT_QTY,
       MAX(CASE
             WHEN fieldname = 'DD_YIELD' THEN
              fieldvalue
           END) AS DD_YIELD,
       MAX(CASE
             WHEN fieldname = 'DD_BLPFX' THEN
              fieldvalue
           END) AS DD_BLPFX,
       MAX(CASE
             WHEN fieldname = 'QJCX_INT_DATE' THEN
              fieldvalue
           END) AS QJCX_INT_DATE,
       MAX(CASE
             WHEN fieldname = 'QJCX_INT_QTY' THEN
              fieldvalue
           END) AS QJCX_INT_QTY,
       MAX(CASE
             WHEN fieldname = 'QJCX_OUT_DATE' THEN
              fieldvalue
           END) AS QJCX_OUT_DATE,
       MAX(CASE
             WHEN fieldname = 'QJCX_OUT_QTY' THEN
              fieldvalue
           END) AS QJCX_OUT_QTY,
       MAX(CASE
             WHEN fieldname = 'QJCX_YIELD' THEN
              fieldvalue
           END) AS QJCX_YIELD,
       MAX(CASE
             WHEN fieldname = 'QJCX_BLPFX' THEN
              fieldvalue
           END) AS QJCX_BLPFX,
       MAX(CASE
             WHEN fieldname = 'ZJ_INT_DATE' THEN
              fieldvalue
           END) AS ZJ_INT_DATE,
       MAX(CASE
             WHEN fieldname = 'ZJ_INT_QTY' THEN
              fieldvalue
           END) AS ZJ_INT_QTY,
       MAX(CASE
             WHEN fieldname = 'ZJ_OUT_DATE' THEN
              fieldvalue
           END) AS ZJ_OUT_DATE,
       MAX(CASE
             WHEN fieldname = 'ZJ_OUT_QTY' THEN
              fieldvalue
           END) AS ZJ_OUT_QTY,
       MAX(CASE
             WHEN fieldname = 'ZJ_YIELD' THEN
              fieldvalue
           END) AS ZJ_YIELD,
       MAX(CASE
             WHEN fieldname = 'ZJ_BLPFX' THEN
              fieldvalue
           END) AS ZJ_BLPFX,
       MAX(CASE
             WHEN fieldname = 'FZ_YIELD' THEN
              fieldvalue
           END) AS FZ_YIELD,
       MAX(CASE
             WHEN fieldname = 'ZTFZ_YIELD' THEN
              fieldvalue
           END) AS ZTFZ_YIELD
  FROM (SELECT t.seqid seqid, t.fieldname fieldname, t.fieldvalue fieldvalue
          FROM Mes_Custreport_Detail t
         where t.fromid = '4221.0000000176796468')
 GROUP BY seqid;