问题涉及到达管理系统 [英] question realated to attenedance management system

问题描述

CREATE TABLE #TempTable2(
 ID int,
 Date nvarchar(30),
 Name char(20))
 INSERT INTO #TempTable2 (ID, Date) 
SELECT att_id, date 
FROM attendance
select *from #TempTable2
SELECT DISTINCT date INTO #Dates
FROM #TempTable2
ORDER BY date
select *from #Dates
DECLARE @cols NVARCHAR(4000)
SELECT  @cols = COALESCE(@cols + ',[' + CONVERT(varchar, date, 106)
				+ ']','[' + CONVERT(varchar, date, 106) + ']')
FROM    #Dates
ORDER BY date
DECLARE @qry NVARCHAR(4000)
SET @qry =
'SELECT * FROM
(SELECT emp_name, status , date
FROM attendance)emp
PIVOT (MAX(Status) FOR date IN (' + @cols + ')) AS stat'

-- Executing the query
EXEC(@qry)

如何在asp.net页面上使用此查询来显示输出page

how to use this query on asp.net page for showing the output on page

推荐答案

首先，你有多个操作，而不仅仅是一个查询。你有：

- 创建临时表

- 插入临时表

- 从临时表中选择

- 再次从临时表中选择

- 再次

- t-sql块

- 以及从字符串执行查询。



在一次通话中执行所有操作是没有意义的。如果你需要执行所有这些，那么使用多个SqlCommand类并执行各个语句。



如果你只需要它们中的最后一个，那么执行它并为示例使用数据适配器来填充数据表。



但在执行其中任何一项之前，我会通过逻辑并尝试简化情况。目前它看起来过于复杂。

First of all you have multiple operations in there, not just a single query. You have:
- create temp table
- insert into temp table
- select from temp table
- again select from temp table
- and again
- t-sql block
- and a query execution from a string.

It wouldn't make sense to execute all that in one call. If you need to execute all of those, then use multiple SqlCommand classes and execute the individual statements.

If you need just the last of them, then execute that and for example use a data adapter to fill a data table.

But before doing either of those I would go through the logic and try to simplify the situation. Currently it looks over-complicated.

这篇关于问题涉及到达管理系统的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！