CODEWITHSUNDEEP
sqloracle-databaseoracle12c
Worakrit Rattanasongtham
Worakrit Rattanasongtham

Reputation: 5

How to use Pivot table with date column

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 ?

Upvotes: 0

Views: 52

Answers (1)

MT0
MT0

Reputation: 168001

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

Upvotes: 1

Related Questions