极品分组排列进行行转列例题
现在更正一下[code=SQL]
--创建测试数据
create table tbl(idno varchar(6),Ban varchar(10),icdate varchar(15),ictime varchar(18))
insert into tbl
select '000008', 'T ', '2007/9/26 ', '01:00:00 ' union all
select '000008', 'T ', '2007/9/26 ', '07:00:00 ' union all
select '000008', 'T ', '2007/9/26 ', '12:30:00 ' union all
select '000008', 'T ', '2007/9/26 ', '13:30:00 ' union all
select '000008', 'T ', '2007/9/26 ', '17:00:00 ' union all
select '000008', 'T ', '2007/9/27 ', '00:30:00 ' union all
select '000011', 'F ', '2007/9/26 ', '13:00:00 ' union all
select '000011', 'F ', '2007/9/26 ', '19:00:00 ' union all
select '000011', 'F ', '2007/9/26 ', '23:00:00 ' union all
select '000011', 'F ', '2007/9/27 ', '01:00:00 ' union all
select '000011', 'F ', '2007/9/27 ', '07:00:00 ' union all
select '000011', 'F ', '2007/9/27 ', '11:30:00 ' union all
select '000008', 'T ', '2007/9/27 ', '07:30:00 ' union all
select '000008', 'T ', '2007/9/27 ', '10:10:00 ' union all
select '000008', 'T ', '2007/9/27 ', '12:30:00 ' union all
select '000011', 'F ', '2007/9/27 ', '07:30:00 ' union all
select '000011', 'F ', '2007/9/27 ', '10:10:00 ' union all
select '000011', 'F ', '2007/9/27 ', '12:10:00 '
--创建临时表一
select a.*,dateadd(minute,-31,convert(datetime,a.icdate+' '+a.ictime,120)) sj into #f from tbl a where ban='t'and ictime<'00:31:00'
insert into #f
select a.*,convert(datetime,a.icdate+' '+a.ictime,120) sj from tbl a where ban='t'and ictime between '01:00:00' and '23:59:59'
union
select a.*,dateadd(day,1,convert(datetime,a.icdate+' '+a.ictime,120)) sj from tbl a where ban='f' and ictime between '13:00:00' and '23:59:00'
union
select a.*,convert(datetime,a.icdate+' '+a.ictime,120) sj from tbl a where ban='f' and ictime between '00:00:00' and '12:30:00'
--创建临时表二
select a.idno,a.ban,a.icdate,a.ictime,a.sj,px=(select count(ictime)+1 from #f where idno=a.idno and ban=a.ban and sj<a.sj and convert(varchar(10),sj,120)=convert(varchar(10),a.sj,120)) into #g from #f a
order by a.idno,a.ban,a.sj
--l转换查询
select idno,min(icdate) icdate,t1=max(case px when 1 then ictime else null end),
t2=max(case px when 2 then ictime else null end),
t3=max(case px when 3 then ictime else null end),
t4=max(case px when 4 then ictime else null end),
t5=max(case px when 5 then ictime else null end),
t6=max(case px when 6 then ictime else null end),
t7=max(case px when 7 then ictime else null end),
t8=max(case px when 8 then ictime else null end)
from #g group by idno,convert(varchar(10),sj,120)
order by icdate
--删除测试数据
drop table tbl,#f,#g
[/code]