常用函数

1、查询scn转换为时间戳

SELECT to_char(scn_to_timestamp(ora_rowscn),'yyyymmdd hh24miss'),txn_time FROM dwr_eda_cf_rep_gls

 2、行转列，pivot

WITH WIP AS
 (SELECT SHIFT_TIMEKEY, PRODUCT_ID, GLS_ID, FACTORY
    FROM DWT_WIP_EOH_GLS
   WHERE SHIFT_TIMEKEY = '20190505 060000'
     AND FACTORY IN ('CF', 'CELL'))
SELECT PRODUCT_ID,CF,CE
  FROM WIP
PIVOT (COUNT(GLS_ID) FOR FACTORY IN('CF' AS CF, 'CELL' AS CE)) P;
--1.FROM WIP 查询出需要的数据
--2.PIVOT 对查询结果做行转列生成新表P，会对GLS_ID、FACTORY列之外的列进行聚合

3、列转行，unpivot

SELECT * FROM P UNPIVOT(GLS_QTY FOR FACTORY IN(CF, CE))

4、多行合并为一条记录，WM_CONCAT

WITH
TEST
AS(
SELECT 1 AS a,'a' AS b FROM dual
UNION ALL SELECT 1 AS a,'b' AS b FROM dual
UNION ALL SELECT 1 AS a,'c' AS b FROM dual
UNION ALL SELECT 2 AS a,'a' AS b FROM dual
UNION ALL SELECT 2 AS a,'d' AS b FROM dual
)
SELECT a,to_char(WM_CONCAT(b)) AS WM FROM TEST GROUP BY a