关于三条记录集合为一条记录的问题

[nnh]

在DataGrid中显示的数据如下 :

编号 姓名 打卡日期 打卡时间
A01 A 03/01/06 12:18
A01 A 03/01/06 13:22
A01 A 03/01/06 17:29


以上表有三行,现在想只汇总一条记录,为:

编号 姓名 打卡日期 打卡时间1 打卡时间2 打卡时间3
A01 A 03/01/06 12:18 13:22 17:29

请问代码该怎样写呢?谢谢.

[活靶子哥哥]

如果A再打一次 呢

[nimeide1234567890]

ding

[yistudio]

sql server数据库的话可以通过函数或存储过程来实现

[boy_north]

那你就只能那个临时表了
然后再绑定到DATAGRID当中

[nimeide1234567890]

分4列显示...

[nnh]

请问怎么做呢?

[7712190]

帮顶把3

[辉说慧语]

select a.bianhao,a.name,a.date,a.time,b.time,c.time
from tb a
left join tb b on a.bianhao=b.biaohao and a.name=b.name and a.date=b.date and a.time<>b.time
left join tb c on a.bianhao-c.bianhao and a.name=c.name and a.date=c.date and
a.time<>c.time and b.time<>c.time

--如 xiahouwen(武眉博<活靶子.NET>)所说,这样不解决根本问题
--可以通过函数或存储过程来实现

[nnh]

那请问"可以通过函数或存储过程来实现", 怎样实现呢?

[xiedan79]

存储过程：
先建一张临时表，字段为 （编号，姓名），同时建一个变量用来存打卡的次数
定义一个游标每读一次时先跟打卡次数比较一下，如果小于或等于打卡次数就 UPATE 一下，
如果大于打卡次数就 alert table ，增加一列，再 UPATE 一下

[hyena041]

首先要确定需要变成几列

如果并不清楚需要在新表中增加几列打卡时间的话，那么就需要使用变量，查询已有表中最大重行数的值，然后根据这个值来建一个临时表，增加相应的打卡时间列

对原表排序后进行遍历，并根据主键增加到新的表中的某一个行
主键开始，就是一个新行，主键变的时候，就开始另一个新行，并结束上一行的数据存入

[xiedan79]

补充：
还需定义一个当前变量记录当前第几次打卡
注意：
需要用到嵌套游标

[xiedan79]

CREATE PROCEDURE SP_Name
....
Parameters
....

AS
CREATE TABLE #T
(
EmployeeNo NVARCHAR(200),
EmployeeName NVARCHAR(200)
)
DECLARE @iTotal AS INT, @iCurrent AS INT
SET @iTotal = 0
SET @iCurrent = 1

DECLARE @EmployeeNO AS NVARCHAR(200), @EmployeeName AS NVARCHAR(200)
DECLARE Cur_Name CURSOR FOR
SELECT EmployeeNO, EmployeeName FROM Employee
OPEN Cur_Name

FETCH NEXT FROM Cur_Name
INTO @EmployeeNO, @EmployeeName
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #T (EmployeeNO, EmployeeName) VALUES (@EmployeeNO, @EmployeeName)
FETCH NEXT FROM Cur_Name
INTO @EmployeeNO, @EmployeeName
END

CLOSE Cur_Name
DEALLOCATE Cur_Name


DECLARE Cur_Name CURSOR FOR
SELECT EmployeeNO FROM #T
OPEN Cur_Name

FETCH NEXT FROM Cur_Name
INTO @EmployeeNO
WHILE @@FETCH_STATUS = 0
BEGIN
SET @iCurrent = 1
DECLARE @Time AS DATETIME
DECLARE Cur_Name1 CURSOR FOR
SELECT Time FROM T_Record WHERE EmployeeNo = @Employee
OPEN Cur_Name1

FETCH NEXT FROM Cur_Name1
INTO @Time
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Cur_Name1
INTO @Time
IF @iTotal >= @iCurrent
BEGIN
DECLARE SQL AS NVARCHAR(4000)
SET SQL = 'UPDATE #T SET Time' + CAST(@iCurrent AS NVARCHAR(5)) + ' = ''' + @Time + ''' WHERE EmployeeNo = ''' + @Employee + ''''
EXECUTE SP_EXECUTESQL SQL
END
ELSE
BEGIN
SET @iTotal = @iTotal + 1
ALERT TABLE ...
DECLARE SQL AS NVARCHAR(4000)
SET SQL = 'UPDATE #T SET Time' + CAST(@iCurrent AS NVARCHAR(5)) + ' = ''' + @Time + ''' WHERE EmployeeNo = ''' + @Employee + ''''
EXECUTE SP_EXECUTESQL SQL
END
SET @iCurrnet = @iCurrent + 1
END
CLOSE Cur_Name1
DEALLOCATE Cur_Name1

FETCH NEXT FROM Cur_Name
INTO @EmployeeNO
END

CLOSE Cur_Name
DEALLOCATE Cur_Name