纵向如何转横向

[NHSS2010]

--构建表a
if object_id('a') is not null drop table a
go
create table a([date] datetime,[id] nvarchar(10),xm nvarchar(10),remark nvarchar(20))
insert a
select '2010-05-17','1001','张三','迟到' union all
select '2010-05-18','1001','张三','迟到' union all
select '2010-05-19','1001','张三','迟到' union all
select '2010-05-17','1002','李四','迟到' union all
select '2010-05-18','1003','王五','迟到' union all
select '2010-05-19','1004','小明','迟到' union all
select '2010-05-17','1005','小红','迟到' union all
select '2010-05-18','1005','小红','迟到'
select * from a

--构建表b
if object_id('b') is not null drop table b
go
create table b([id] nvarchar(10),[ip] nvarchar(10))
insert b
select '1001','456' union all
select '1002','457' union all
select '1003','458' union all
select '1004','459' union all
select '1005','460'
select * from b

--查询结果1:
select a.[id],a.xm,ip,[date]
from a join b on a.[id]=b.[id]
where remark='迟到' and date between dateadd(day,-3,getdate()) and getdate()
order by a.[id]

--现想把 查询结果1 变为:
[id] xm ip 前天 昨天 今天
1001 张三 456 2010-05-17 2010-05-18 2010-05-19
1002 李四 457 2010-05-17 null null
1003 王五 458 null 2010-05-18 null
1004 小明 459 null null 2010-05-19
1005 小红 460 2010-05-17 2010-05-18 null

请各位大侠帮忙,十分感谢

[NHSS2010]

在线等啊
期待中...

[dawugui]

create table a([date] datetime,[id] nvarchar(10),xm nvarchar(10),remark nvarchar(20))
insert a
select '2010-05-17','1001','张三','迟到' union all
select '2010-05-18','1001','张三','迟到' union all
select '2010-05-19','1001','张三','迟到' union all
select '2010-05-17','1002','李四','迟到' union all
select '2010-05-18','1003','王五','迟到' union all
select '2010-05-19','1004','小明','迟到' union all
select '2010-05-17','1005','小红','迟到' union all
select '2010-05-18','1005','小红','迟到'  
create table b([id] nvarchar(10),[ip] nvarchar(10))
insert b
select '1001','456' union all
select '1002','457' union all
select '1003','458' union all
select '1004','459' union all
select '1005','460'

select b.id , 
       xm = (select top 1 xm from a where a.id = b.id order by a.date),
       b.ip,
       前天 = (select top 1 date from a where a.id = b.id and datediff(day , date,getdate()) = 2),
       昨天 = (select top 1 date from a where a.id = b.id and datediff(day , date,getdate()) = 1),
       今天 = (select top 1 date from a where a.id = b.id and datediff(day , date,getdate()) = 0)
from b

drop table a , b

/*
id         xm         ip         前天                                                     昨天                                                     今天                                                     
---------- ---------- ---------- ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ 
1001       张三         456        2010-05-17 00:00:00.000                                2010-05-18 00:00:00.000                                2010-05-19 00:00:00.000
1002       李四         457        2010-05-17 00:00:00.000                                NULL                                                   NULL
1003       王五         458        NULL                                                   2010-05-18 00:00:00.000                                NULL
1004       小明         459        NULL                                                   NULL                                                   2010-05-19 00:00:00.000
1005       小红         460        2010-05-17 00:00:00.000                                2010-05-18 00:00:00.000                                NULL

（所影响的行数为 5 行）

*/

[Ultra95599]

create proc [dbo].[IpTimeGetUser]
@parToday datetime
as
declare 
@cols as nvarchar(max),
@sql as nvarchar(max),
@today datetime;

set @today = @parToday;

set @cols = STUFF(
(select N',' + QUOTENAME(times) as [text()]
from (select distinct CAST(DATEPART(HOUR,StatisticsTime) as nvarchar)
+':'+CAST(DATEPART(MINUTE,StatisticsTime) as nvarchar) as times 
from UserCountDetail where StatisticsTime >= convert(nvarchar(10),@today,120) 
and StatisticsTime <  convert(nvarchar(10),@today+1,120) ) AS Y
order by times for XML PATH('')),1,1,N'');

set @sql = N'select * from(select * from (select ip,CAST(DATEPART(HOUR,StatisticsTime) as nvarchar)
+ '':'' + CAST(DATEPART(MINUTE,StatisticsTime) as nvarchar) as times,UserCount 
from UserCountDetail where StatisticsTime >= ''' + convert(nvarchar(10),@today,120) + N''' 
and StatisticsTime < '''+ convert(nvarchar(10),@today+1,120) +''') 
as sd pivot(sum(UserCount) for times in(' + @cols + N')) as pvt) as c ;'