跨表查询
我有一张员工出勤表 - 比如说 - OATT。以下是结构和示例数据，
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
我需要以下结果：
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
我知道这可以使用数据透视查询来完成，但我需要一个动态查询来针对指定的日期范围执行此操作。我正在使用 SQL Server 2005。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
PIVOT 关键字可能会有所帮助，但即使如此，您仍然需要在运行查询之前知道列是什么。这通常意味着运行两个查询：一个查询获取列名称列表，第二个查询实际获取结果。
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.
解决方案之一是让 Query 将 TSQL 代码打印到变量中
然后调用 exec @variable
One of solution is that make Query to print out TSQL code into variable
and after that to call exec @variable