Pivot table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Pivot table

Author

Topic

c2
Starting Member

2 Posts

Posted - 2008-10-15 : 00:33:47

I have a table named employee inout and having below data;

Employee code In date In time Out time
01 1-Oct-08 7:00 5:00
01 2-Oct-08 6:30 5:00
01 3-Oct-08 7:10 4:39
02 1-Oct-08 6:00 3:00
02 2-Oct-08 7:00 5:00
02 3-Oct-08 8:00 6:00

I want to see the data in following format;

Employee code Day 1 Day 2 Day 3
In Out In Out In Out
01 7:00 5:00 6:30 5:00 7:10 4:39
02 6:00 3:00 7:00 5:00 8:00 6:00

Any help will be highly appreciated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-15 : 03:55:07

[code]SELECT EmployeeCode,
MAX(CASE WHEN Seq=1 THEN InTime ELSE NULL END) AS Day1InTime,
MAX(CASE WHEN Seq=1 THEN OutTime ELSE NULL END) AS Day1OutTime,
MAX(CASE WHEN Seq=2 THEN InTime ELSE NULL END) AS Day2InTime,
MAX(CASE WHEN Seq=2 THEN InTime ELSE NULL END) AS Day2OutTime,
MAX(CASE WHEN Seq=3 THEN InTime ELSE NULL END) AS Day3InTime,
MAX(CASE WHEN Seq=3 THEN InTime ELSE NULL END) AS Day3OutTime
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY EmployeeCode ORDER BY InDate) AS Seq,*
FROM YourTable)t
GROUP BY EmployeeCode[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources