sql server pivot table query

Question

we are trying to convert an attendance log data into a pivot table . for the sake of simplicity, the actual data is in the form :

EmployeeId, InOrOut,  DateTime
1            0        2019-01-01 08:00:00
1            1        2019-01-01 17:00:00
1            0        2019-01-02 08:00:00
1            1        2019-01-02 17:00:00
2            0        2019-01-01 08:00:00
2            1        2019-01-01 17:00:00

and we need to make it like so:

EmployeeId, Date,        InTime , OutTime
1           2019-01-01   08:00    17:00
1           2019-01-02   08:00    17:00
2           2019-01-01   08:00    17:00

however the query we have made doesnt seem to work like this. the query is as under:

SELECT * FROM
(
SELECT aml.EnrollNumber, aml.A_Date, aml.InOutMode, aml.A_Time
 FROM dbo.Attendence_Machines_LOG aml) AS AttendanceTable
PIVOT (
     max(A_Date)
    FOR InOutMode in ([1],[0])
) as PivotTable

There are many confusions regarding pivot and personally cannot find much tutorials.

how do we tell the query that on which basis is the data put in a new row (for example in this case, how will we tell the query to seperate the records according to date and employeeid)

any help appreciated

It is not a pivot. Pivot means turning row data into column names. You've got a great answer by @Yogesh Sharma. Upvoted. — StepUp, Feb 28 '19 at 11:48
I think what you need is first want to get all the records of time for each date then show first record as "in" and last record as "out" for each date. — DarkRob, Feb 28 '19 at 11:52

Yogesh Sharma · Accepted Answer · 2019-02-28T11:43:01.460

4

You can do aggregation :

SELECT aml.EnrollNumber, aml.A_Date,
       MAX(CASE WHEN aml.InOutMode = 1 THEN aml.A_Time END),
       MAX(CASE WHEN aml.InOutMode = 0 THEN aml.A_Time END)
FROM dbo.Attendence_Machines_LOG AS aml
GROUP BY aml.EnrollNumber, aml.A_Date;

EDIT : After question edit made :

SELECT aml.EnrollNumber, CAST(aml.A_Date AS DATE),
       MAX(CASE WHEN aml.InOutMode = 1 THEN aml.A_Time END),
       MAX(CASE WHEN aml.InOutMode = 0 THEN aml.A_Time END)
FROM dbo.Attendence_Machines_LOG AS aml
GROUP BY aml.EnrollNumber, CAST(aml.A_Date AS DATE);

edited Feb 28 '19 at 11:43

answered Feb 28 '19 at 11:24

Yogesh Sharma

49,870
5
26
52

thank you for the answer. will try that as well. however was also just curious to seize the opportunity and learn how to do pivot tables as well :) – Abdul Ali Feb 28 '19 at 11:25

score 1 · Answer 2 · answered Feb 28 '19 at 12:02

If you want this is pivot then try this:

    GO
    create table #temptable ( empid int, inorout int, attdate datetime )
    Go

    insert into #temptable ( empid, inorout, attdate )
    values ( 1 ,           0        ,'2019-01-01 08:00:00'),
    (1            ,1        ,'2019-01-01 17:00:00')
    ,(1            ,0        ,'2019-01-02 08:00:00')
    ,(1            ,1        ,'2019-01-02 17:00:00')
    ,(2            ,0        ,'2019-01-01 08:00:00')
    ,(2            ,1        ,'2019-01-01 17:00:00')


    select * from #temptable

    select empid, atdate, [0], [1]  from (
    select empid,inorout,  CAST(attdate as DATE) as atdate, attdate from #temptable ) as d
    pivot 
    ( max(attdate) for inorout in ( [0], [1] )
    ) as pv

    go

    drop table #temptable

If any confusion feel free to ask.

Note: But this will work only if you have only 1 In and 1 Out per day as your given raw data.

sql server pivot table query

2 Answers2