SQL 纵向表转为横向表

需求：

纵向表

A列 B列 C列  
张三 1 2011-10-1 8:30
张三 2 2011-10-1 17:30
李四 1 2011-10-1 8:30
李四 2 2011-10-1 17:30
王五 1 2011-10-1 8:30
张三 1 2011-10-3 8:30
张三 2 2011-10-3 17:30
张三 1 2011-10-4 8:30

B列的1,2代表不同的状态，转换下，使上面的表变成
A列       B列            C列  

张三 2011-10-1 8:30 2011-10-1 17:30
李四 2011-10-1 8:30 2011-10-1 17:30
王五 2011-10-1 8:30 null
张三 2011-10-3 8:30 2011-10-3 17:30

没有的则显示空，同一个人，同一天的合并一起

解决方案：

declare @T table (A varchar(4),B int,C datetime)
insert into @T
select '张三',1,'2011-10-1 8:30' union all
select '张三',2,'2011-10-1 17:30' union all
select '李四',1,'2011-10-1 8:30' union all
select '李四',2,'2011-10-1 17:30' union all
select '王五',1,'2011-10-1 8:30' union all
select '张三',1,'2011-10-3 8:30' union all
select '张三',2,'2011-10-3 17:30' union all
select '张三',1,'2011-10-4 8:30'

DECLARE @CUR_aaa CURSOR
DECLARE @DayID NVARCHAR(10)
DECLARE @bb TABLE (DayID NVARCHAR(10),A NVARCHAR(50),B NVARCHAR(50),C NVARCHAR(50))


SET @CUR_aaa = CURSOR FOR 
SELECT DISTINCT CONVERT(NVARCHAR(10),CONVERT(DATETIME,C),120) as DayID FROM @T


OPEN @CUR_aaa
FETCH next FROM @CUR_aaa into @DayID
WHILE (@@FETCH_STATUS=0) 
BEGIN
INSERT INTO @bb(DayID,A) SELECT DISTINCT @DayID,A FROM @T
FETCH next FROM @CUR_aaa into @DayID
END


Close @CUR_aaa
DEALLOCATE @CUR_aaa

UPDATE @bb SET B=CONVERT(NVARCHAR(20),CONVERT(DATETIME,a.C),120) FROM @T a,@bb b where a.A=b.A and CONVERT(NVARCHAR(10),CONVERT(DATETIME,a.C),120)=b.DayID and a.B='1'
UPDATE @bb SET C=CONVERT(NVARCHAR(20),CONVERT(DATETIME,a.C),120) FROM @T a,@bb b where a.A=b.A and CONVERT(NVARCHAR(10),CONVERT(DATETIME,a.C),120)=b.DayID and a.B='2'


DELETE FROM @bb WHERE b IS NULL AND C IS NULL
SELECT A,B,C FROM @bb order by CONVERT(NVARCHAR(10),CONVERT(DATETIME,B),120),A


-----------------------------------------------------
运行结果如下：

横向表：

A         B                  C
张三 2011-10-01 08:30:00 2011-10-01 17:30:00
王五 2011-10-01 08:30:00 NULL
李四 2011-10-01 08:30:00 2011-10-01 17:30:00
张三 2011-10-03 08:30:00 2011-10-03 17:30:00
张三 2011-10-04 08:30:00 NULL