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--自定义排序 可有可无
bug怎么这么多!
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战