Making a Pivot in oracle SQL

Question

I am having some trouble making a pivot in oracle sql. I have a table with workdate, employee and their shift for a day. This table is called SHIFT. I want that in this pivot, the workdate becomes the column headings, the employee become the row headings and the shifts be the day so you can see the employee shift per day.

My current problem is that i have a query but the pivot requires that i put an aggregate function. The query is as follows:

select employee, workdate, shift from shift
pivot (
        max(shift)
        for employee
        in (
            1,2,3,4
        )
)

SO i did this and it is bringing back all the columns and not in a pivoted format. Current Strucutre:

employee	workdate	shift
employee1	date1	shift1
employee1	date2	shift2
employee2	date1	shift1
employee2	date2	shift2
employee3	date1	shift1
employee3	date2	shift2
employee4	date1	shift1
employee4	date2	shift2
employee5	date1	shift1
employee5	date2	shift2

My desired results are:

	date1	date2	date3
employee1	shift1	shift2	shift3
employee2	shift1	shift2	shift3
employee3	shift1	shift2	shift3
employee4	shift1	shift2	shift3

Thanks you for your answers.

If you need to pivot by dates, then you need to specify `workdate` in `for` and dates in `in` — astentx, Aug 18 '21 at 18:18
I want the workdae to run across and the employee to run down but the shift must be between as the data (shift per day) but i have to use an aggreate function in the pivot and it just produces the wrong results — Basudev Singh, Aug 18 '21 at 18:25

score 1 · Accepted Answer · answered Aug 18 '21 at 18:27

You want:

select employee,
       date1,
       date2,
       date3
from   shift
pivot (
 max(shift)
 for workdate
 in (
   DATE '2021-08-17' AS date1,
   DATE '2021-08-18' AS date2,
   DATE '2021-08-19' AS date3
 )
)

Which, for your sample data:

CREATE TABLE shift (employee, workdate, shift) AS
SELECT 'employee1', DATE '2021-08-17', 'shift1' FROM DUAL UNION ALL
SELECT 'employee1', DATE '2021-08-18', 'shift2' FROM DUAL UNION ALL
SELECT 'employee2', DATE '2021-08-17', 'shift1' FROM DUAL UNION ALL
SELECT 'employee2', DATE '2021-08-18', 'shift2' FROM DUAL UNION ALL
SELECT 'employee3', DATE '2021-08-17', 'shift1' FROM DUAL UNION ALL
SELECT 'employee3', DATE '2021-08-18', 'shift2' FROM DUAL UNION ALL
SELECT 'employee4', DATE '2021-08-17', 'shift1' FROM DUAL UNION ALL
SELECT 'employee4', DATE '2021-08-18', 'shift2' FROM DUAL UNION ALL
SELECT 'employee5', DATE '2021-08-17', 'shift1' FROM DUAL UNION ALL
SELECT 'employee5', DATE '2021-08-18', 'shift2' FROM DUAL;

Outputs:

EMPLOYEE DATE1 DATE2 DATE3

employee2 shift1 shift2

employee1 shift1 shift2

employee3 shift1 shift2

employee5 shift1 shift2

employee4 shift1 shift2

Note: your data had zero rows for shift3.

db<>fiddle here

This works but i also discovered that you can do the same pivot in an interactive report and add an LOV to the shift column and it worked how i wanted it to work — Basudev Singh, Sep 02 '21 at 17:43

NewToGo · Answer 2 · 2021-08-18T19:04:51.880

1

If you need where clause on employee along with pivot on workdate then you will write

 SELECT * FROM (
    SELECT employee, workdate, shift 
        FROM shift WHERE employee IN ('employee1', 'employee2', 'employee3')
   )
   pivot (
       max(shift)
       for workdate
       in (
        ('date1','date2','date3','date4')
      )
  )

edited Aug 18 '21 at 19:04

answered Aug 18 '21 at 19:01

NewToGo

83
5

This works. Thank you. It is another way of doing the query – Basudev Singh Sep 02 '21 at 17:43

Making a Pivot in oracle SQL

2 Answers2