how to transpose and pivot in oracle sql

Question

i am trying to flip the data from a table , here is a screenshot to explain how it looks. on the left we have database table and on right how i want to derive it

i have so far tried using pivot, and case statements but was not able to get the view i need, please guide

score 1 · Answer 1 · answered Jun 29 '22 at 12:22

This is typically a task that is performed in the presentation-layer and not in the database.

If you must do it in SQL then you can use:

SELECT *
FROM   shift_t
PIVOT (
  MAX(shift)
  FOR shift_date IN (
    DATE '2022-07-01' AS "07/01/2022",
    DATE '2022-07-02' AS "07/02/2022",
    DATE '2022-07-03' AS "07/03/2022",
    DATE '2022-07-04' AS "07/04/2022",
    DATE '2022-07-05' AS "07/05/2022",
    DATE '2022-07-06' AS "07/06/2022",
    DATE '2022-07-07' AS "07/07/2022",
    DATE '2022-07-08' AS "07/08/2022",
    DATE '2022-07-09' AS "07/09/2022"
  )
)

Which, for the sample data, outputs:

LOCATION NAME 07/01/2022 07/02/2022 07/03/2022 07/04/2022 07/05/2022 07/06/2022 07/07/2022 07/08/2022 07/09/2022

HYD Rose C W W C C C C C W

Note: You must hard-code the date values. They cannot be provided dynamically using a PIVOT statement. If you want a dynamic pivot then look at this question (and then ignore it and perform the task in the presentation-layer and not in SQL).

db<>fiddle here

how to transpose and pivot in oracle sql

1 Answers1