SQL2005高手帮忙看看,该如何解决

SQL2005高手帮忙看看
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

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

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

------解决方案--------------------

SQL code

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 行受影响)
*/

------解决方案--------------------

SQL code


--> 测试数据: @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

SQL2005高手帮忙看看,该如何解决

相关推荐