这个行转列怎么写

Question

CREATE  TABLE 测试
(
员工编号 nvarchar(20),
日期  nvarchar(30),
时间 nvarchar(30)
)
insert into 测试 select '001','2011/10/01','00:00:00'
insert into 测试 select '001','2011/10/01','15:00:00'
insert into 测试 select '002','2011/10/01','08:00:00'
insert into 测试 select '002','2011/10/01','12:00:00'
insert into 测试 select '002','2011/10/01','13:30:00'
insert into 测试 select '002','2011/10/01','17:30:00'
insert into 测试 select '003','2011/10/01','08:30:00'
insert into 测试 select '003','2011/10/01','12:30:00'
insert into 测试 select '003','2011/10/01','17:30:00'我要的结果是
员工编号, 日期, 时间1......时间完

Answer 1

加个自增列再MAX(CASE..WHEN）就可以了 懒得写了

Answer 2

CREATE  TABLE 测试
(
员工编号 nvarchar(20),
日期  nvarchar(30),
时间 nvarchar(30),
val int
)
insert into 测试 select '001','2011/10/01','00:00:00',1
insert into 测试 select '001','2011/10/01','15:00:00',1
insert into 测试 select '002','2011/10/01','08:00:00',1
insert into 测试 select '002','2011/10/01','12:00:00',1
insert into 测试 select '002','2011/10/01','13:30:00',1
insert into 测试 select '002','2011/10/01','17:30:00',1
insert into 测试 select '003','2011/10/01','08:30:00',1
insert into 测试 select '003','2011/10/01','12:30:00',1
insert into 测试 select '003','2011/10/01','17:30:00',1
go
declare @s nvarchar(max)
--获得列标头[a],[b]
select @s=isnull(@s+',','')+'['+ 时间 +']' from(
select distinct [时间] from 测试
)t
exec('select [员工编号],日期,'+@s+'from 测试 pivot (sum([val]) for [时间] in('+@s+'))b')
/*
员工编号                 日期                             00:00:00    08:00:00    08:30:00    12:00:00    12:30:00    13:30:00    15:00:00    17:30:00
-------------------- ------------------------------ ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
001                  2011/10/01                     1           NULL        NULL        NULL        NULL        NULL        1           NULL
002                  2011/10/01                     NULL        1           NULL        1           NULL        1           NULL        1
003                  2011/10/01                     NULL        NULL        1           NULL        1           NULL        NULL        1(3 行受影响)*/go
drop table 测试

Answer 3

CREATE  TABLE 测试
(
员工编号 nvarchar(20),
日期  nvarchar(30),
时间 nvarchar(30)
)
insert into 测试 select '001','2011/10/01','00:00:00'
insert into 测试 select '001','2011/10/01','15:00:00'
insert into 测试 select '002','2011/10/01','08:00:00'
insert into 测试 select '002','2011/10/01','12:00:00'
insert into 测试 select '002','2011/10/01','13:30:00'
insert into 测试 select '002','2011/10/01','17:30:00'
insert into 测试 select '003','2011/10/01','08:30:00'
insert into 测试 select '003','2011/10/01','12:30:00'
insert into 测试 select '003','2011/10/01','17:30:00'
GO
DECLARE @s NVARCHAR(4000),@i NVARCHAR(2)
SELECT TOP 1 @s='',@i=COUNT(*) FROM 测试 GROUP BY 员工编号,日期 ORDER BY COUNT(*) descWHILE @i>0
SELECT @s=N',[时间'+@i+N']=max(case when row='+@i+N' then 时间 else '''' end)'+@s,@i=@i-1
EXEC('select 员工编号,日期'+@s+' from (SELECT *,row=(SELECT COUNT(*) FROM 测试 WHERE 员工编号=a.员工编号 AND 日期=a.日期 AND 时间<=a.时间) FROM 测试 AS a)AS a group by 员工编号,日期')/*
员工编号 日期 时间1 时间2 时间3 时间4
001 2011/10/01 00:00:00 15:00:00
002 2011/10/01 08:00:00 12:00:00 13:30:00 17:30:00
003 2011/10/01 08:30:00 12:30:00 17:30:00
*/

Answer 4

嗯
谢谢晴天
insert into 测试 select '004','2011/10/01','17:30:00'
insert into 测试 select '004','2011/10/01','17:30:00'
insert into 测试 select '004','2011/10/01','17:30:00'
我再加这三行数据用6楼的方法.结果只显示最后一个这是为什么?

Answer 5

需要生成臨時表，SQL2005可用row_number代替臨時表DECLARE @s NVARCHAR(4000),@i NVARCHAR(2)
SELECT TOP 1 @s='',@i=COUNT(*) FROM 测试 GROUP BY 员工编号,日期 ORDER BY COUNT(*) descIF OBJECT_ID('Tempdb..#')IS NOT NULL
DROP TABLE #
SELECT *,ID=IDENTITY(INT,1,1) INTO # FROM 测试 AS a ORDER BY 员工编号,日期,时间WHILE @i>0
    SELECT @s=N',[时间'+@i+N']=max(case when row='+@i+N' then 时间 else '''' end)'+@s,@i=@i-1
EXEC('select 员工编号,日期'+@s+' from (SELECT *,row=(SELECT COUNT(*) FROM # WHERE 员工编号=a.员工编号 AND 日期=a.日期 AND ID<=a.ID) FROM # AS a)AS a group by 员工编号,日期')/*
员工编号 日期 时间1 时间2 时间3 时间4
001 2011/10/01 00:00:00 15:00:00
002 2011/10/01 08:00:00 12:00:00 13:30:00 17:30:00
003 2011/10/01 08:30:00 12:30:00 17:30:00
004 2011/10/01 17:30:00 17:30:00 17:30:00
*/

Answer 6

SQL2005這樣用DECLARE @s NVARCHAR(4000),@i NVARCHAR(2)
SELECT TOP 1 @s='',@i=COUNT(*) FROM 测试 GROUP BY 员工编号,日期 ORDER BY COUNT(*) descWHILE @i>0
    SELECT @s=N',[时间'+@i+N']=max(case when row='+@i+N' then 时间 else '''' end)'+@s,@i=@i-1
EXEC('select 员工编号,日期'+@s+' from (SELECT *,row=ROW_NUMBER()OVER(PARTITION BY 员工编号,日期 ORDER BY 时间) FROM 测试)AS a group by 员工编号,日期')

Answer 7

CREATE  TABLE 测试
(
员工编号 nvarchar(20),
日期  nvarchar(30),
时间 nvarchar(30)
)
insert into 测试 select '001','2011/10/01','00:00:00'
insert into 测试 select '001','2011/10/01','15:00:00'
insert into 测试 select '002','2011/10/01','08:00:00'
insert into 测试 select '002','2011/10/01','12:00:00'
insert into 测试 select '002','2011/10/01','13:30:00'
insert into 测试 select '002','2011/10/01','17:30:00'
insert into 测试 select '003','2011/10/01','08:30:00'
insert into 测试 select '003','2011/10/01','12:30:00'
insert into 测试 select '003','2011/10/01','17:30:00'insert into 测试 select '004','2011/10/01','17:30:00'
insert into 测试 select '004','2011/10/01','17:30:00'
insert into 测试 select '004','2011/10/01','17:30:00'
--select ROW_NUMBER()over(partition by 员工编号,日期 order by 时间)row,* from 测试
GO
DECLARE @s NVARCHAR(4000),@i NVARCHAR(2)
SELECT TOP 1 @s='',@i=COUNT(*) FROM 测试 GROUP BY 员工编号,日期 ORDER BY COUNT(*) descWHILE @i>0
    SELECT @s=N',[时间'+@i+N']=max(case when row='+@i+N' then 时间 else '''' end)'+@s,@i=@i-1
    
EXEC('select 员工编号,日期'+@s+' from (SELECT *,row=ROW_NUMBER()over(partition by 员工编号,日期 order by 时间) FROM 测试 AS a)AS b group by 员工编号,日期')
/*
员工编号                 日期                             时间1                            时间2                            时间3                            时间4
-------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
001                  2011/10/01                     00:00:00                       15:00:00                                                      
002                  2011/10/01                     08:00:00                       12:00:00                       13:30:00                       17:30:00
003                  2011/10/01                     08:30:00                       12:30:00                       17:30:00                       
004                  2011/10/01                     17:30:00                       17:30:00                       17:30:00                       (4 行受影响)*/
go
drop table 测试