日期:2014-05-18 浏览次数:20564 次
if object_id('[tb]') is not null drop table [tb] go create table [tb]([A] varchar(4),[B] int,[C] datetime) insert [tb] 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' declare @sql varchar(8000) select @sql=isnull(@sql+',','') +'max(case when b='+ltrim(b)+' then c end) as [date'+ltrim(b)+']' from (select distinct b from tb) t exec ('select a,'+@sql+' from tb group by a') /* a date1 date2 ---- ----------------------- ----------------------- 李四 2011-10-01 08:30:00.000 2011-10-01 17:30:00.000 王五 2011-10-01 08:30:00.000 NULL 张三 2011-10-03 08:30:00.000 2011-10-03 17:30:00.000 (3 行受影响) */
------解决方案--------------------
--> 测试数据: @T 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' select a.*,b.C列 from @T a left join @T b on a.A列=b.A列 where a.B列=1 and (b.B列=2 or b.B列 is null) and convert(varchar(10),a.C列,120)=convert(varchar(10),b.C列,120) /* A列 B列 C列 C列 ---- ----------- ----------------------- ----------------------- 张三 1 2011-10-01 08:30:00.000 2011-10-01 17:30:00.000 李四 1 2011-10-01 08:30:00.000 2011-10-01 17:30:00.000 张三 1 2011-10-03 08:30:00.000 2011-10-03 17:30:00.000 */
------解决方案--------------------
那我加个日期的栏位,这样就是四个栏位了,你到时候select的时候,抓三个栏位就好了
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 aa
OPEN @CUR_aaa
FETCH next FROM @CUR_aaa into @DayID
WHILE (@@FETCH_STATUS=0)
BEGIN
INSERT INTO @bb(DayID,A) SELECT DISTINCT A FROM aa
FETCH next FROM @CUR_aaa into @DayID
END
Close @CUR_aaa
DEALLOCATE @CUR_aaa
UPDATE @bb SET B=a.C FROM aa a,@bb b where a.A=b.A and CONVERT(NVARCHAR(10),CONVERT(DATETIME,a.DayID),120)=b.DayID and a.B='1'
UPDATE @bb SET C=a.C FROM aa a,@bb b where a.A=b.A and CONVERT(NVARCHAR(10),CONVERT(DATETIME,a.DayID),120)=b.DayID and a.B='2'
SELECT A,B,C FROM @bb