1

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.

Improve this question
2
  • 1
    What differentiates between the different rows with the same data? You need to be able to give your query something to differentiate between the various rows of product/code combinations. From what you have here, there doesn't appear to be any differences to key off of. Commented Sep 3, 2018 at 19:39
  • Hi Chris data will be differentiated by the Date, I have given only one date in example, It will have dates for last 14 days from current date. Commented Jan 29, 2019 at 20:58

1 Answer 1

Reset to default
5

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).

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I was not able to understand the last part clearly , what would the ordering column contain ?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.