CODEWITHSUNDEEP
sqloracle-databasepivot
Abhijeet Kumar
Abhijeet Kumar

Reputation: 11

Oracle Query - making columns values as column headers for multiple values

I have following table

Product Code Value time
ABC     ASK  15    31-AUG-18
ABC     BID  18    31-AUG-18
ABC     MID  15    31-AUG-18
ABC     ASK  11    31-AUG-18
ABC     BID  12    31-AUG-18
ABC     MID  10    31-AUG-18
ABC     ASK  1.3   31-AUG-18
ABC     BID  1.8   31-AUG-18
ABC     MID  1.5   31-AUG-18

and I want output as, 

Product ASK BID MID Date
ABC     15  18  15  31-AUG-18
ABC     11  12  10  31-AUG-18
ABC     1.3 1.8 1.5 31-AUG-18

I tried this using pivot, but it is summing up all the values which is not the desired output. I am able to get something like this but this is not what is desired,

Product ASK   BID   MID   Date
ABC     27.3  31.8  26.5  31-AUG-18

Any suggestions will be very helpful.

Upvotes: 1

Views: 1413

Answers (1)

MT0
MT0

Reputation: 168001

Use the ROW_NUMBER analytic function to give each code per group a unique identifier:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table_name ( Product, Code, Value, time ) AS
SELECT 'ABC', 'ASK', 15.0, DATE '2018-08-31' FROM DUAL UNION ALL
SELECT 'ABC', 'BID', 18.0, DATE '2018-08-31' FROM DUAL UNION ALL
SELECT 'ABC', 'MID', 15.0, DATE '2018-08-31' FROM DUAL UNION ALL
SELECT 'ABC', 'ASK', 11.0, DATE '2018-08-31' FROM DUAL UNION ALL
SELECT 'ABC', 'BID', 12.0, DATE '2018-08-31' FROM DUAL UNION ALL
SELECT 'ABC', 'MID', 10.0, DATE '2018-08-31' FROM DUAL UNION ALL
SELECT 'ABC', 'ASK',  1.3, DATE '2018-08-31' FROM DUAL UNION ALL
SELECT 'ABC', 'BID',  1.8, DATE '2018-08-31' FROM DUAL UNION ALL
SELECT 'ABC', 'MID',  1.5, DATE '2018-08-31' FROM DUAL;

Query 1:

SELECT Product,
       ask,
       bid,
       mid,
       time
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER ( PARTITION BY Product, Code, time ORDER BY ROWNUM ) AS rn
  FROM   table_name t
)
PIVOT ( MAX( value ) FOR Code IN (
  'ASK' AS ask,
  'BID' AS bid,
  'MID' AS mid
) )
ORDER BY product, time, rn

Results:

| PRODUCT | ASK | BID | MID |                 TIME |
|---------|-----|-----|-----|----------------------|
|     ABC |  15 |  18 |  15 | 2018-08-31T00:00:00Z |
|     ABC |  11 |  12 |  10 | 2018-08-31T00:00:00Z |
|     ABC | 1.3 | 1.8 | 1.5 | 2018-08-31T00:00:00Z |

Ideally you should have another column to contain the ordering as using ROWNUM is not guaranteed to give the rows in a consistent order if Oracle reads the rows in a different order (or if row movement is enabled).

Upvotes: 5

Related Questions