需求:
纵向表
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