Oracle pivot（行转列） 和unpivot（列转行）函数

行转列 pivot

select * from (      
 select t.product_sn, t.test_item, t.test_value
   from t_wip_device_interface t
  where t.mo_no='302-MO1903075049-1401'
  and t.test_result='0'
  ) pivot(max(test_value) for test_item in('CHGCur1', 'OV_Value', 'IR_Value', 'CycleCount', 'ProDate',
        'Factory', 'ProductStr', 'Chemical', 'Characteristics', 'SerialNo',
        'MBtemp', 'ICtemp', 'SoftVersion', 'HardwareVersion', 'Capacity',
        'MBVolt', 'ICVolt', 'ICVoltOff', 'NCV_VoltValue', 'NCV_CurrValue',
        'ICCHGCurr', 'ICCHGCurrOff', 'NDV_CurrValue', 'ICDSGCurr',
        'ICDSGCurrOff', 'NDV_VoltValue', 'OCCHG_PCurr', 'OCCHG_PTime',
        'OCDSG_PCurr', 'OCDSG_PTime', 'NDV_CurrValue_10A', 'ICDSGCurr_10A',
        'ICDSGCurrOff_10A', 'NDV_VoltValue_10A', 'PCBtemp', 'Celltemp',
        'OCOVP_Value', 'SWLowVolt', 'SWHighVolt', 'Key4LowVolt',
        'Key4HighVolt'))

列转行 unpivot

select sfc_no, result, test_item, test_vale
  from (
        select *
          from (select t.sfc_no,
                        t.result， D12 "IR",
                        D22 "OCV (DVM)",
                        D21 "OCV (GG)",
                        D23 "Voltage Accuracy",
                        D89 "Vcell_1",
                        D88 "Vcell_2",
                        D33 "Bank Offset Voltage",
                        D32 "Charge Current Accuracy",
                        D29 "Discharge Current Accuracy",
                        D45 "FCC",
                        D47 "RSOC",
                        D70 "Shipmode Output Voltage",
                        row_number() OVER(PARTITION BY t.sfc_no ORDER BY t.create_date desc) rn
                   from sfc_datagroup_tmp t
                  where t.oper in ('6676ef52596f426c8745f2642a72f0a2')
                    and t.sfc_no in ('10866770020000034910'))
         where rn = 1
        ) unpivot(test_vale for test_item in ("IR", "OCV (DVM)", "OCV (GG)", "Voltage Accuracy", "Vcell_1", "Vcell_2", "Bank Offset Voltage", "Charge Current Accuracy","Discharge Current Accuracy", "FCC", "RSOC", "Shipmode Output Voltage"))