oracle 格式转换 <行转列列转行>

一.效果图

 转换后

 

二.sql语法

　　1.先是将你要的数据显示出来（当成一张表）

　　如 

  SELECT time_test time1,
         STRUCTCODE,
         SUBSTRUCTCODE,
         COUNT (*) 片数,
         ROUND (AVG (COW_LOP1_AVG_K), 2) AS COW_LOP1_AVG_K,
         ROUND (AVG (COW_LOP1_AVG), 2) AS SMP_LOP1,
         ROUND (AVG (COW_LOP2_AVG), 2) AS SMP_LOP2,
         ROUND (AVG (COW_VF1_AVG), 2) SMP_VF1,
         ROUND (AVG (COW_WLD1_AVG), 2) SMP_WLD1,
         ROUND (AVG (COW_ESD04_YIELD), 2) MM400,
         ROUND (AVG (COW_VF4_AVG), 2) SMP_VF4,
         ROUND (AVG (COW_HW1_AVG), 2) SMP_HW1,
         ROUND (AVG (COW_IR1_YIELD), 2) SMP_IR1_YIELD,
         ROUND (AVG (COW_VZ1_AVG), 2) SMP_VZ1,
         ROUND (AVG (COW_ESD01_YIELD), 2) ESD01_YIELD,
         ROUND (AVG (COW_ESD03_YIELD), 2) ESD03_YIELD,
         ROUND (AVG (droop), 2) AS Droop
    FROM (SELECT DISTINCT SUBSTR (dm.COW_STARTTIME, 1, 10) time_test,
                          SUBSTR (EPI_OUTTIME, 1, 10) time_build,
                          SUBSTR (dm.lot, 3, 6) recipe,
                          b.STRUCTCODE,
                          b.SUBSTRUCTCODE,
                          dm.COW_LOP1_AVG,
                          dm.COW_LOP2_AVG,
                          dm.COW_LOP1_AVG / dm.COW_LOP2_AVG AS droop,
                          dm.COW_LOP1_AVG_K,
                          dm.COW_VF1_AVG,
                          dm.COW_WLD1_AVG,
                          dm.COW_WLD2_AVG,
                          dm.COW_VF4_AVG,
                          dm.COW_HW1_AVG,
                          dm.COW_IR1_YIELD,
                          dm.COW_VZ1_AVG,
                          dm.COW_ESD01_YIELD,
                          dm.COW_ESD03_YIELD,
                          dm.COW_ESD04_YIELD
            FROM CHIPDM.DM_SMP_PROBER dm,
                 (SELECT lot,
                         wo,
                         COMPONENTID,
                         STRUCTCODE,
                         SUBSTRUCTCODE
                    FROM epi.MES_EPI_WO_BOOK, epi.MES_WIP_COMP_CREATE
                   WHERE epi.MES_EPI_WO_BOOK.lot =
                            epi.MES_WIP_COMP_CREATE.CREATELOT) b,
                 mes_wms_lot_nonactive wms
           WHERE     dm.epicomponentid = wms.waferid
                 AND dm.COW_STARTTIME IS NOT NULL
                 AND dm.epicomponentid = b.COMPONENTID
                 AND dm.COW_LOP2_AVG <> 0
                 AND dm.COW_LOP2_AVG IS NOT NULL
                 AND SUBSTR (dm.COW_STARTTIME, 1, 10) >= '2019/12/09'
                 AND SUBSTR (dm.COW_STARTTIME, 1, 10) <= '2020/01/02'
                 AND SUBSTR (dm.lot, 3, 6) IN ('W1028K')
                 AND SUBSTR (b.COMPONENTID, 12, 1) IN ('L', 'P', 'Y')
                 AND SUBSTR (dm.LOT, 2, 1) IN ('H',
                                               'I',
                                               'K',
                                               'L')
                 AND b.STRUCTCODE IN ('W4L2D06', 'W4L2D05')
                 AND b.SUBSTRUCTCODE IN ('01',
                                         '02',
                                         '03',
                                         '04'))
GROUP BY STRUCTCODE, SUBSTRUCTCODE, time_test
ORDER BY time_test

2.套语法（将你的表放入  你的sql表）

 SELECT *
    FROM (SELECT time1,--根据时间排序
                 structcode || '-' || substructcode ss,--不做转换的 
                 CASE
                    WHEN category = '片数' THEN '片数'
                    WHEN category = 'COW_LOP1_AVG_K' THEN 'COW_LOP1_AVG_K'
                    WHEN category = 'SMP_LOP1' THEN 'SMP_LOP1'
                    WHEN category = 'SMP_LOP2' THEN 'SMP_LOP2'
                    WHEN category = 'SMP_VF1' THEN 'SMP_VF1'
                    WHEN category = 'SMP_WLD1' THEN 'SMP_WLD1'
                    WHEN category = 'MM400' THEN 'MM400'
                    WHEN category = 'SMP_VF4' THEN 'SMP_VF4'
                    WHEN category = 'SMP_HW1' THEN 'SMP_HW1'
                    WHEN category = 'SMP_IR1_YIELD' THEN 'SMP_IR1_YIELD'
                    WHEN category = 'SMP_VZ1' THEN 'SMP_VZ1'
                    WHEN category = 'ESD01_YIELD' THEN 'ESD01_YIELD'--
                    WHEN category = 'DROOP' THEN 'DROOP'--你想转哪个字段就填哪个字段 填了的下面 UNPIVOT (VALUE FOR category IN  (这里面就得填)
                 END
                    category,--你的SQL表里 要有字段 上面才能填
                 VALUE AS aa
            FROM (  
            
                       --你的SQL表
                  
                  ) UNPIVOT (VALUE
                                      FOR category
                                      IN  (片数,
                                          COW_LOP1_AVG_K,
                                          SMP_LOP1,
                                          SMP_LOP2,
                                          SMP_VF1,
                                          SMP_WLD1,
                                          MM400,
                                          SMP_VF4,
                                          SMP_HW1,
                                          SMP_IR1_YIELD,
                                          SMP_VZ1,    
                                          ESD01_YIELD,--和上面的字段名要相同
                                        
                                          Droop))) PIVOT (MAX (aa)
                                                   FOR time1
                                                   IN  ('2019/12/09' AS "2019/12/09",
                                                       '2019/12/10' AS "2019/12/10",
                                                       '2019/12/11' AS "2019/12/11",
                                                       '2019/12/12' AS "2019/12/12",
                                                       '2019/12/13' AS "2019/12/13",
                                                       '2019/12/14' AS "2019/12/14",
                                                       '2019/12/15' AS "2019/12/15",
                                                       '2019/12/16' AS "2019/12/16",
                                                       '2019/12/17' AS "2019/12/17",
                                                       '2019/12/18' AS "2019/12/18",
                                                       '2019/12/19' AS "2019/12/19",
                                                       '2019/12/20' AS "2019/12/20",
                                                       '2019/12/21' AS "2019/12/21",
                                                       '2019/12/22' AS "2019/12/22",
                                                       '2019/12/23' AS "2019/12/23",
                                                       '2019/12/24' AS "2019/12/24",
                                                       '2019/12/25' AS "2019/12/25",
                                                       '2019/12/26' AS "2019/12/26",
                                                       '2019/12/27' AS "2019/12/27",
                                                       '2019/12/28' AS "2019/12/28",
                                                       '2019/12/29' AS "2019/12/29",
                                                       '2019/12/30' AS "2019/12/30",
                                                       '2019/12/31' AS "2019/12/31",
                                                       '2020/01/01' AS "2020/01/01",
                                                       '2020/01/02' AS "2020/01/02"))--分组列  这个我是后台sql 拼接的 动态的 
ORDER BY ss,--排序不要在意
         DECODE (category,
                 '片数', 1,
                 'COW_LOP1_AVG_K', 2,
                 'SMP_LOP1', 3,
                 'SMP_LOP2', 4,
                 'SMP_VF1', 5,
                 'SMP_WLD1', 6,
                 'MM400', 7,
                 'SMP_VF4', 8,
                 'SMP_HW1', 9,
                 'SMP_IR1_YIELD', 10,
                 'SMP_VZ1', 11,
                 'ESD01_YIELD', 12,
                 'Droop', 13) ASC--自定义排序

 

 3.合成的sql

/* Formatted on 2020/1/17 10:30:59  */
  SELECT *
    FROM (SELECT time1,
                 structcode || '-' || substructcode ss,
                 CASE
                    WHEN category = '片数' THEN '片数'
                    WHEN category = 'COW_LOP1_AVG_K' THEN 'COW_LOP1_AVG_K'
                    WHEN category = 'SMP_LOP1' THEN 'SMP_LOP1'
                    WHEN category = 'SMP_LOP2' THEN 'SMP_LOP2'
                    WHEN category = 'SMP_VF1' THEN 'SMP_VF1'
                    WHEN category = 'SMP_WLD1' THEN 'SMP_WLD1'
                    WHEN category = 'MM400' THEN 'MM400'
                    WHEN category = 'SMP_VF4' THEN 'SMP_VF4'
                    WHEN category = 'SMP_HW1' THEN 'SMP_HW1'
                    WHEN category = 'SMP_IR1_YIELD' THEN 'SMP_IR1_YIELD'
                    WHEN category = 'SMP_VZ1' THEN 'SMP_VZ1'
                    WHEN category = 'ESD01_YIELD' THEN 'ESD01_YIELD'
                    WHEN category = 'DROOP' THEN 'DROOP'
                 END
                    category,
                 VALUE AS aa
            FROM (  SELECT time_test time1,
                           STRUCTCODE,
                           SUBSTRUCTCODE,
                           COUNT (*) 片数,
                           ROUND (AVG (COW_LOP1_AVG_K), 2) AS COW_LOP1_AVG_K,
                           ROUND (AVG (COW_LOP1_AVG), 2) AS SMP_LOP1,
                           ROUND (AVG (COW_LOP2_AVG), 2) AS SMP_LOP2,
                           ROUND (AVG (COW_VF1_AVG), 2) SMP_VF1,
                           ROUND (AVG (COW_WLD1_AVG), 2) SMP_WLD1,
                           ROUND (AVG (COW_ESD04_YIELD), 2) MM400,
                           ROUND (AVG (COW_VF4_AVG), 2) SMP_VF4,
                           ROUND (AVG (COW_HW1_AVG), 2) SMP_HW1,
                           ROUND (AVG (COW_IR1_YIELD), 2) SMP_IR1_YIELD,
                           ROUND (AVG (COW_VZ1_AVG), 2) SMP_VZ1,
                           ROUND (AVG (COW_ESD01_YIELD), 2) ESD01_YIELD,
                           ROUND (AVG (COW_ESD03_YIELD), 2) ESD03_YIELD,
                           ROUND (AVG (droop), 2) AS Droop
                      FROM (SELECT DISTINCT
                                   SUBSTR (dm.COW_STARTTIME, 1, 10) time_test,
                                   SUBSTR (EPI_OUTTIME, 1, 10) time_build,
                                   SUBSTR (dm.lot, 3, 6) recipe,
                                   b.STRUCTCODE,
                                   b.SUBSTRUCTCODE,
                                   dm.COW_LOP1_AVG,
                                   dm.COW_LOP2_AVG,
                                   dm.COW_LOP1_AVG / dm.COW_LOP2_AVG AS droop,
                                   dm.COW_LOP1_AVG_K,
                                   dm.COW_VF1_AVG,
                                   dm.COW_WLD1_AVG,
                                   dm.COW_WLD2_AVG,
                                   dm.COW_VF4_AVG,
                                   dm.COW_HW1_AVG,
                                   dm.COW_IR1_YIELD,
                                   dm.COW_VZ1_AVG,
                                   dm.COW_ESD01_YIELD,
                                   dm.COW_ESD03_YIELD,
                                   dm.COW_ESD04_YIELD
                              FROM CHIPDM.DM_SMP_PROBER dm,
                                   (SELECT lot,
                                           wo,
                                           COMPONENTID,
                                           STRUCTCODE,
                                           SUBSTRUCTCODE
                                      FROM epi.MES_EPI_WO_BOOK,
                                           epi.MES_WIP_COMP_CREATE
                                     WHERE epi.MES_EPI_WO_BOOK.lot =
                                              epi.MES_WIP_COMP_CREATE.CREATELOT)
                                   b,
                                   mes_wms_lot_nonactive wms
                             WHERE     dm.epicomponentid = wms.waferid
                                   AND dm.COW_STARTTIME IS NOT NULL
                                   AND dm.epicomponentid = b.COMPONENTID
                                   AND dm.COW_LOP2_AVG <> 0
                                   AND dm.COW_LOP2_AVG IS NOT NULL
                                   AND SUBSTR (dm.COW_STARTTIME, 1, 10) >=
                                          '2019/12/09'
                                   AND SUBSTR (dm.COW_STARTTIME, 1, 10) <=
                                          '2020/01/02'
                                   AND SUBSTR (dm.lot, 3, 6) IN ('W1028K')
                                   AND SUBSTR (b.COMPONENTID, 12, 1) IN ('L',
                                                                         'P',
                                                                         'Y')
                                   AND SUBSTR (dm.LOT, 2, 1) IN ('H',
                                                                 'I',
                                                                 'K',
                                                                 'L')
                                   AND b.STRUCTCODE IN ('W4L2D06', 'W4L2D05')
                                   AND b.SUBSTRUCTCODE IN ('01',
                                                           '02',
                                                           '03',
                                                           '04'))
                  GROUP BY STRUCTCODE, SUBSTRUCTCODE, time_test
                  ORDER BY time_test) UNPIVOT (VALUE
                                      FOR category
                                      IN  (片数,
                                          COW_LOP1_AVG_K,
                                          SMP_LOP1,
                                          SMP_LOP2,
                                          SMP_VF1,
                                          SMP_WLD1,
                                          MM400,
                                          --BS,
                                          SMP_VF4,
                                          SMP_HW1,
                                          SMP_IR1_YIELD,
                                          SMP_VZ1,
                                          ESD01_YIELD,
                                          --ESD03_YIELD,
                                          Droop))) PIVOT (MAX (aa)
                                                   FOR time1
                                                   IN  ('2019/12/09' AS "2019/12/09",
                                                       '2019/12/10' AS "2019/12/10",
                                                       '2019/12/11' AS "2019/12/11",
                                                       '2019/12/12' AS "2019/12/12",
                                                       '2019/12/13' AS "2019/12/13",
                                                       '2019/12/14' AS "2019/12/14",
                                                       '2019/12/15' AS "2019/12/15",
                                                       '2019/12/16' AS "2019/12/16",
                                                       '2019/12/17' AS "2019/12/17",
                                                       '2019/12/18' AS "2019/12/18",
                                                       '2019/12/19' AS "2019/12/19",
                                                       '2019/12/20' AS "2019/12/20",
                                                       '2019/12/21' AS "2019/12/21",
                                                       '2019/12/22' AS "2019/12/22",
                                                       '2019/12/23' AS "2019/12/23",
                                                       '2019/12/24' AS "2019/12/24",
                                                       '2019/12/25' AS "2019/12/25",
                                                       '2019/12/26' AS "2019/12/26",
                                                       '2019/12/27' AS "2019/12/27",
                                                       '2019/12/28' AS "2019/12/28",
                                                       '2019/12/29' AS "2019/12/29",
                                                       '2019/12/30' AS "2019/12/30",
                                                       '2019/12/31' AS "2019/12/31",
                                                       '2020/01/01' AS "2020/01/01",
                                                       '2020/01/02' AS "2020/01/02"))
ORDER BY ss,
         DECODE (category,
                 '片数', 1,
                 'COW_LOP1_AVG_K', 2,
                 'SMP_LOP1', 3,
                 'SMP_LOP2', 4,
                 'SMP_VF1', 5,
                 'SMP_WLD1', 6,
                 'MM400', 7,
                 'SMP_VF4', 8,
                 'SMP_HW1', 9,
                 'SMP_IR1_YIELD', 10,
                 'SMP_VZ1', 11,
                 'ESD01_YIELD', 12,
                 'Droop', 13) ASC

 

只要你Ctrl+C  Ctrl+V 放到对应的语法中 就能实现

  SELECT *
    FROM (SELECT time1,--根据时间排序
                 structcode || '-' || substructcode ss,--不做转换的 
                 CASE
                    WHEN category = '片数' THEN '片数'
                    WHEN category = 'COW_LOP1_AVG_K' THEN 'COW_LOP1_AVG_K'
               --你想转哪个字段就填哪个字段 填了的下面 UNPIVOT (VALUE FOR category IN  (这里面就得填)
                 END
                    category,VALUE AS aa --aa只是个自定义名称
            FROM (  
            
                       --放入你的表sql
                  
                  ) UNPIVOT (VALUE FOR category
                                      IN  (片数,
                                          COW_LOP1_AVG_K -- category列的数据 字段名称数量要和case when一样
                                            ))) PIVOT (MAX (aa) FOR time1 
                                                   IN  ('2019/12/09' AS "2019/12/09",
                                                       '2019/12/10' AS "2019/12/10",
                                                       '2019/12/11' AS "2019/12/11"
                                                        ))--你的列
ORDER BY ss,--排序不要在意
         DECODE (category,
                 '片数', 1,
                 'COW_LOP1_AVG_K', 2) ASC--自定义排序 可有可无
posted @ 2020-01-17 11:11  changlinlo  阅读(285)  评论(0)  编辑  收藏  举报