SQL Server 2008列转行问题

我统计出公时表的数据,如下

create table operator(
begin_time datetime,
end_time datetime,
op_id char(4),
work_time int
)

insert into operator select '2013-03-01 00:00:00','2013-03-02 00:00:00','1001',7
insert into operator select '2013-03-01 00:00:00','2013-03-02 00:00:00','1002',8
insert into operator select '2013-03-01 00:00:00','2013-03-02 00:00:00','1003',6
insert into operator select '2013-03-01 00:00:00','2013-03-02 00:00:00','2001',8
insert into operator select '2013-03-01 00:00:00','2013-03-02 00:00:00','2002',8
insert into operator select '2013-03-01 00:00:00','2013-03-02 00:00:00','2003',7
insert into operator select '2013-03-01 00:00:00','2013-03-02 00:00:00','2004',8
insert into operator select '2013-03-01 00:00:00','2013-03-02 00:00:00','3001',6
insert into operator select '2013-03-01 00:00:00','2013-03-02 00:00:00','3002',7
insert into operator select '2013-03-01 00:00:00','2013-03-02 00:00:00','3003',8
insert into operator select '2013-03-01 00:00:00','2013-03-02 00:00:00','3004',5
insert into operator select '2013-03-01 00:00:00','2013-03-02 00:00:00','3005',6
insert into operator select '2013-03-02 00:00:00','2013-03-03 00:00:00','1001',7
insert into operator select '2013-03-02 00:00:00','2013-03-03 00:00:00','1002',8
insert into operator select '2013-03-02 00:00:00','2013-03-03 00:00:00','1003',6
insert into operator select '2013-03-02 00:00:00','2013-03-03 00:00:00','1005',9
insert into operator select '2013-03-02 00:00:00','2013-03-03 00:00:00','1007',6
insert into operator select '2013-03-02 00:00:00','2013-03-03 00:00:00','2002',9
insert into operator select '2013-03-02 00:00:00','2013-03-03 00:00:00','2003',7
insert into operator select '2013-03-02 00:00:00','2013-03-03 00:00:00','2004',5
insert into operator select '2013-03-02 00:00:00','2013-03-03 00:00:00','2006',3
insert into operator select '2013-03-02 00:00:00','2013-03-03 00:00:00','3003',8
insert into operator select '2013-03-02 00:00:00','2013-03-03 00:00:00','3005',7
insert into operator select '2013-03-02 00:00:00','2013-03-03 00:00:00','3007',10

然后我想把这个数据,转换成如下两种表的格式,请问如何处理?

第一张结果表中,手误多输入了一行开始结束时间,后面却是空的.(请大家自动屏蔽之),谢谢!

第一个结果的格式， 你先看看？合适不合适？

With myCTE AS (
select
ROW_NUMBER() over(partition by begin_time, end_time, LEFt(op_id,1) ORDER BY op_id) AS NO,
operator.*
from
operator
)
SELECt
begin_time AS [开始时间],
end_time AS [结束时间],
MAX(CASE WHEN LEFt(op_id,1) = '1' THEN op_id else '' END) AS [一组工号],
MAX(CASE WHEN LEFt(op_id,1) = '1' THEN work_time else NULL END) AS [工作时长],
MAX(CASE WHEN LEFt(op_id,1) = '2' THEN op_id else '' END) AS [二组工号],
MAX(CASE WHEN LEFt(op_id,1) = '2' THEN work_time else NULL END) AS [工作时长],
MAX(CASE WHEN LEFt(op_id,1) = '3' THEN op_id else '' END) AS [三组工号],
MAX(CASE WHEN LEFt(op_id,1) = '3' THEN work_time else NULL END) AS [工作时长]
FROM
myCTE
GROUP BY
begin_time, end_time, NO

第二个结果的格式

SELECt
begin_time AS [开始时间],
end_time AS [结束时间],
SUM( CASE WHEN LEFt(op_id,1) = '1' THEN 1 ELSE 0 END ) AS [一组上班人数],
SUM( CASE WHEN LEFt(op_id,1) = '2' THEN 1 ELSE 0 END ) AS [二组上班人数],
SUM( CASE WHEN LEFt(op_id,1) = '3' THEN 1 ELSE 0 END ) AS [三组上班人数]
FROM
operator
GROUP BY
begin_time, end_time

1。将program files下的sql安装目录删除(此处如果有重要的数据,请先备份) c:\program files\microsoft sql server\80\tools。 c:\program files\microsoft sql server\mssql。 2。注册表中的相关信息删除: hkey_local_machine\software\microsoft\mssqlserver。 hkey_local_machine\system\currentcontrolset\services\msdtc。 3。hkey_local_machine\system\currentcontrolset\control\session manager中找到pendingfilerenameoperations项目,并删除它。这样就可以清除安装暂挂项目 4。hkey_local_machine\software\microsoft\windows\currentversion\setup 删除exceptioncomponents 这个项目下的内容可能很多,只有在上面几个办法不行的情况下,使用此方法(注意不要全部删除) 删除内容前先看下内容说明 5。windows 目录中的 sqlstp.log 文件,该文件列出了安装程序所执行的操作的详细信息,并包含安装期间遇到的所有错误。通过检查该文件,可以详细了解安装在什么地方失败、为什么失败。 6。sql安装的时的错误信息保存在一个叫errorlog的日志文件中,默认情况下该文件位于 program files\microsoftsql server\mssql\log 目录中。该错误日志包含安装程序试图启动sql server时sql server 所遇到的错误,这些信息可以帮助您深入检查错误原因。 7。需要检查的另一个组件是 microsoft 数据访问组件 (mdac) 安装程序,它作为 sql server 2000 安装程序的一部分启动。sql server 2000 安装程序会安装 mdac 2.6。mdac 安装程序会创建名为 dasetup.log 的单独的日志文件;您可以查看此日志文件并确保 mdac 安装程序没有出现问题。

如以上回答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息，可以点下面链接进行举报！

点此我要举报以上问答信息