Cross-tab Query
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.
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.
One of solution is that make Query to print out TSQL code into variable and after that to call exec @variable
精彩评论