CODEWITHSUNDEEP
sql-server-2008pivotpivot-table
Devs
Devs

Reputation: 65

SQL Server 2008 Pivot Query - Datetime

I have this record with time in/out for employee in multiple rows, how to query a pivot with same id, name but different time in/out?

Just like on image below:

See this image

Upvotes: 1

Views: 165

Answers (1)

gofr1
gofr1

Reputation: 15977

You can use pivoting like:

;WITH dtrTable AS (  --just a test sample of your table
SELECT *
FROM (VALUES
(1, 'emp1', '2016-10-20', '2016-10-20 10:00:00.000', '2016-10-20 15:00:00.000'),
(1, 'emp1', '2016-10-20', '2016-10-20 15:30:00.000', '2016-10-20 17:00:00.000'),
(1, 'emp1', '2016-10-20', '2016-10-20 18:30:00.000', '2016-10-20 19:00:00.000'),
(2, 'emp2', '2016-10-20', '2016-10-20 10:00:00.000', '2016-10-20 19:00:00.000'),
(2, 'emp2', '2016-10-20', '2016-10-20 21:00:00.000', '2016-10-20 22:00:00.000'),
(2, 'emp2', '2016-10-21', '2016-10-20 11:00:00.000', '2016-10-20 21:00:00.000')
) as t(empId, empName, dtrDate, dtrIn, dtrOut)
)

SELECT *
FROM (
    SELECT  empId, 
            empName, 
            dtrDate,
            [Columns]+seq as [Columns],
            [Values]
    FROM (
        SELECT *,
                CAST(ROW_NUMBER() OVER (PARTITION BY empId, dtrDate ORDER BY dtrIn) as nvarchar(100)) as seq
        FROM dtrTable
    ) as t
    UNPIVOT (
        [Values] FOR [Columns] IN ([dtrIn],[dtrOut])
    ) as unpvt
) as d
PIVOT (
    MAX([Values]) FOR [Columns] IN ([dtrIn1],[dtrOut1],[dtrIn2],[dtrOut2],[dtrIn3],[dtrOut3])
) as pvt

Output:

empId   empName dtrDate     dtrIn1                  dtrOut1                 dtrIn2                  dtrOut2                 dtrIn3                  dtrOut3
1       emp1    2016-10-20  2016-10-20 10:00:00.000 2016-10-20 15:00:00.000 2016-10-20 15:30:00.000 2016-10-20 17:00:00.000 2016-10-20 18:30:00.000 2016-10-20 19:00:00.000
2       emp2    2016-10-20  2016-10-20 10:00:00.000 2016-10-20 19:00:00.000 2016-10-20 21:00:00.000 2016-10-20 22:00:00.000 NULL                    NULL
2       emp2    2016-10-21  2016-10-20 11:00:00.000 2016-10-20 21:00:00.000 NULL                    NULL                    NULL                    NULL

Unpivot part will give you this table:

empId   empName dtrDate     Columns Values
1       emp1    2016-10-20  dtrIn1  2016-10-20 10:00:00.000
1       emp1    2016-10-20  dtrOut1 2016-10-20 15:00:00.000
1       emp1    2016-10-20  dtrIn2  2016-10-20 15:30:00.000
1       emp1    2016-10-20  dtrOut2 2016-10-20 17:00:00.000
1       emp1    2016-10-20  dtrIn3  2016-10-20 18:30:00.000
1       emp1    2016-10-20  dtrOut3 2016-10-20 19:00:00.000
2       emp2    2016-10-20  dtrIn1  2016-10-20 10:00:00.000
2       emp2    2016-10-20  dtrOut1 2016-10-20 19:00:00.000
2       emp2    2016-10-20  dtrIn2  2016-10-20 21:00:00.000
2       emp2    2016-10-20  dtrOut2 2016-10-20 22:00:00.000
2       emp2    2016-10-21  dtrIn1  2016-10-20 11:00:00.000
2       emp2    2016-10-21  dtrOut1 2016-10-20 21:00:00.000

Here I use ROW_NUMBER() with partitioning to give some order to ins and outs. And also numbers, generated by ROW_NUMBER(), helps to create columns, we cannot use same column names in pivot part.

Upvotes: 2

Related Questions