Cross-tab Query

Question

Hello,

I have a table with employee attendance - lets say - OATT. Following is the structure and sample data

AttDate EmpId EmpName AttCode InTime OutTime
01-10-2009 1 Jain, Rahul P 0900 1830
02-10-2009 1 Jain, Rahul P 0900 1830
03-10-2009 1 Jain, Rahul P 0900 1830
04-10-2009 1 Jain, Rahul P 0900 1830
05-10-2009 1 Jain, Rahul P 0900 1830
06-10-2009 1 Jain, Rahul WO 0900 1830
07-10-2009 1 Jain, Rahul WO 0900 1830
08-10-2009 1 Jain, Rahul P 0900 1830
09-10-2009 1 Jain, Rahul L 0900 1830
10-10-2009 1 Jain, Rahul P 0900 1830
01-10-2009 1 Jain, Rahul A 0900 1830

I need the following result:

EmpId 01-10 02-10 03-10 04-10 05-10
1 P P P P P
2 P P P L P
3 P P P P A

I know that this can be accomplished using pivot queries, but I need a dynamic query to do it for a specified range of dates. I am using SQL Server 2005.

Thanks & Regards Rahul Jain

Answer 1

The PIVOT keyword might help, but even with that you still need to know what your columns are before running the query. That usually means running two queries: one to get a list of columns names and then a second to actually get your results.

Answer 2

See similar question/answer with dynamic sql.

Answer 3

One of solution is that make Query to print out TSQL code into variable and after that to call exec @variable