CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
abautista
abautista

Reputation: 2780

How to pivot a date column into multiple columns by day?

I have the following table that displays the hours that employees have worked in an assigned job per day.

   emp_num   |  job   |      initial_date     |  worked_hours
  ----------  --------  ---------------------  -------------
    100001     VESS_10   2019-01-01 06:00:00         2
    100001     VESS_20   2019-01-01 08:00:00         1 
    100001     VESS_30   2019-01-02 06:00:00         1
    100002     VESS_20   2019-01-02 08:00:00         2 
    100002     VESS_20   2019-01-03 10:00:00         2
    100003     VESS_30   2019-01-01 11:00:00         1

I would like to display the following result:

   emp_num   |  job   |      2019-01-01    |    2019-01-02      |    2019-01-03   |  worked_hours
  ----------  --------  -------------------   ---------------   ------------------  ------------
    100001     VESS_10   2019-01-01 06:00:00     NULL                  NULL             2
    100001     VESS_20   2019-01-01 08:00:00     NULL                  NULL             1
    100001     VESS_30          NULL          2019-01-02 06:00:00      NULL             1
    100002     VESS_20          NULL          2019-01-02 08:00:00      NULL             2
    100002     VESS_20          NULL             NULL              2019-01-03 10:00:00  2
    100003     VESS_30   2019-01-01 11:00:00     NULL                  NULL             1

What I tried was to pivot the table with the following query:

select * from 
    (
     select emp_num, job, initial_date worked_hours
     from transactions
     where initial_date between '2019-04-21 00:00:00' and '2019-04-27 23:59:59'
)
as hours_table
pivot (
    sum(worked_hours)
    for initial_date in (['my problem comes here'])])
)

The problem with the code from above is that in the for statement I want to declare the dates in 7 columns because they represent each day of the week, however, I do not know how to classify each row based on the date and then assign it in the correct column.

Any help, comment or suggestion is greatly appreciated.

Upvotes: 0

Views: 279

Answers (1)

mkRabbani
mkRabbani

Reputation: 16908

Try this-

SELECT *
FROM
(
    SELECT B.emp_num,B.initial_date,B.Date_Only,B.job,
    (
        SELECT SUM(worked_hours) 
        FROM transactions 
        WHERE emp_num = B.emp_num AND  job = B.job
    ) worked_hours
    FROM 
    (
        SELECT emp_num,job,initial_date,worked_hours,CAST(initial_date AS DATE) Date_Only 
        FROM transactions
    )B
    WHERE initial_date between '2018-12-30 00:00:00' and '2019-01-05 23:59:59'
) A
PIVOT
(
    MAX(initial_date)
    FOR Date_Only IN ([2019-01-01],[2019-01-02],[2019-01-03])
)PVT

Upvotes: 1

Related Questions