0

I have an attendance table whose structure is like this.

CREATE TABLE ATTENDANCE(
EmployeeID VARCHAR(10),
LogDate DATETIME);

Supposse following values are there in the table:

INSERT INTO ATTENDANCE VALUES('1','2014-06-25 09:30:00:00')
INSERT INTO ATTENDANCE VALUES('1','2014-06-25 13:20:00:00')
INSERT INTO ATTENDANCE VALUES('1','2014-06-25 15:40:00:00')
INSERT INTO ATTENDANCE VALUES('1','2014-06-25 18:21:00:00')

INSERT INTO ATTENDANCE VALUES('2','2014-06-25 08:31:00:00')
INSERT INTO ATTENDANCE VALUES('2','2014-06-25 14:21:00:00')
INSERT INTO ATTENDANCE VALUES('2','2014-06-25 16:43:00:00')
INSERT INTO ATTENDANCE VALUES('2','2014-06-25 19:22:00:00')

INSERT INTO ATTENDANCE VALUES('1','2014-06-26 09:30:00:00')
INSERT INTO ATTENDANCE VALUES('1','2014-06-26 13:20:00:00')
INSERT INTO ATTENDANCE VALUES('1','2014-06-26 15:40:00:00')
INSERT INTO ATTENDANCE VALUES('1','2014-06-26 18:21:00:00')

INSERT INTO ATTENDANCE VALUES('2','2014-06-26 08:31:00:00')
INSERT INTO ATTENDANCE VALUES('2','2014-06-26 14:21:00:00')
INSERT INTO ATTENDANCE VALUES('2','2014-06-26 16:43:00:00')
INSERT INTO ATTENDANCE VALUES('2','2014-06-26 19:22:00:00')

I want to display the data like below:

Emp ID    Attendance Date    Log 1          Log 2          Log 3          Log 4
1         25-04-2014         09:30:00:00    13:20:00:00    15:40:00:00    18:21:00:00
2         25-04-2014         08:31:00:00    14:21:00:00    16:43:00:00    19:22:00:00
1         26-04-2014         09:30:00:00    13:20:00:00    15:40:00:00    18:21:00:00
2         26-04-2014         08:31:00:00    14:21:00:00    16:43:00:00    19:22:00:00

Please help me frame this query.

Thanks,

Rajesh

Joachim Isaksson
  • 176,943
  • 25
  • 281
  • 294
user3779522
  • 21
  • possible duplicate of [SQL Server PIVOT examples?](http://stackoverflow.com/questions/24470/sql-server-pivot-examples) – usr Jun 26 '14 at 13:39
  • Here the Attendance Date column and Log1, Log2,....... could be multiple in numbers and couldn't be ascertained before framing the query. That is where I am stuck. – user3779522 Jun 26 '14 at 17:09
  • The only way then will be a dynamic query. Does the log columns are numerated always starting from 1, and does al the employees have the same number of logs every date? – Guillermo Gutiérrez Jun 26 '14 at 21:56
  • Yes, every time the report would be generated, the log columns will be numerated from 1. Employee may have different number of logs every day. – user3779522 Jun 27 '14 at 06:12

0 Answers0