I have a table in which information about a machine running time.
This is how the table looks like:
Date canot be fixed. (Base on seach condition date period) :
MachineCODE DATE RUNNING
-----------------------------
CNC001 2019-01-04 10
CNC001 2019-02-04 10
CNC002 2019-02-04 20
CNC003 2019-04-04 5
CNC004 2019-05-04 5
Is it possible to get output like this:
Itemcode 01/04/2019 02/04/2019 03/04/2019 04/04/2019 05/04/2019
-------------------------------------------------------------------------
CNC001 10 10
CNC002 20
CNC003 5
CNC004 5
But after doing some research in Google, I have found out its possible using pivot table... Could you please help ?
Use PIVOT:
Oracle Setup:
CREATE TABLE table_name ( MachineCODE, "DATE", RUNNING ) AS
SELECT 'CNC001', DATE '2019-01-04', 10 FROM DUAL UNION ALL
SELECT 'CNC001', DATE '2019-02-04', 10 FROM DUAL UNION ALL
SELECT 'CNC002', DATE '2019-02-04', 20 FROM DUAL UNION ALL
SELECT 'CNC003', DATE '2019-04-04', 5 FROM DUAL UNION ALL
SELECT 'CNC004', DATE '2019-05-04', 5 FROM DUAL
Query:
SELECT *
FROM table_name
PIVOT ( MAX( RUNNING ) FOR "DATE" IN (
DATE '2019-01-04' AS "2019-01-04",
DATE '2019-02-04' AS "2019-02-04",
DATE '2019-03-04' AS "2019-03-04",
DATE '2019-04-04' AS "2019-04-04",
DATE '2019-05-04' AS "2019-05-04"
) )
Output:
MACHINECODE | 2019-01-04 | 2019-02-04 | 2019-03-04 | 2019-04-04 | 2019-05-04 :---------- | ---------: | ---------: | ---------: | ---------: | ---------: CNC001 | 10 | 10 | null | null | null CNC002 | null | 20 | null | null | null CNC003 | null | null | null | 5 | null CNC004 | null | null | null | null | 5
db<>fiddle here