使用decode实现

 select MONITORINGPOINT,
                   ACQTM,
                   max(coalesce(DECODE(NAME, 'EquCa', MONITORVALUE, null), 0))   as EQUCA,
                   max(coalesce(DECODE(NAME, 'LosFact', MONITORVALUE, null), 0)) as LOSFACT,
                  max(coalesce(DECODE(NAME, 'RisAmp', MONITORVALUE, null), 0)) as RISAMP,
                  max(coalesce(DECODE(NAME, 'TotAmp', MONITORVALUE, null), 0)) as TOTAMP,
                  max(coalesce(DECODE(NAME, 'CalAmp', MONITORVALUE, null), 0)) as CALAMP
            from IMP_TELEMETERING
            where MONITORINGTYPEID = 'A386' --and MONITORINGPOINT = '0304B13009999A386109001'
            group by MONITORINGPOINT, ACQTM;

使用case when实现

 SELECT
     E_CODE,
     E_TIME,
     SUM( CASE E_TYPE WHEN 0 THEN E_VALUE ELSE 0 END ) AS WATER_ENERGY,--水耗
     SUM( CASE E_TYPE WHEN 1 THEN E_VALUE ELSE 0 END ) AS ELE_ENERGY,--电耗
     SUM( CASE E_TYPE WHEN 2 THEN E_VALUE ELSE 0 END ) AS HEAT_ENERGGY--热耗
 FROM
     THTF_ENERGY_TEST
 GROUP BY 
     E_CODE,E_TIME

嵌套case when

注意嵌套case when的时候似乎只能使用一个then，然后就要接else了。

 case when PATROL_CLASSIFY = 1 then
     case when  WORK_SITE_NAMES like '%线%' then 2 else 1 end
     else PATROL_CLASSIFY end grid_type,

参考

SQL中的case when then else end用法