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]gocreate table [tb]([A] varchar(4),[B] int,[C] datetime)insert [tb]select '张三',1,'2011-10-1 8:30' union allselect '张三',2,'2011-10-1 17:30' union allselect '李四',1,'2011-10-1 8:30' union allselect '李四',2,'2011-10-1 17:30' union allselect '王五',1,'2011-10-1 8:30' union allselect '张三',1,'2011-10-3 8:30' union allselect '张三',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) texec ('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--&gt; 测试数据: @Tdeclare @T table (A列 varchar(4),B列 int,C列 datetime)insert into @Tselect '张三',1,'2011-10-1 8:30' union allselect '张三',2,'2011-10-1 17:30' union allselect '李四',1,'2011-10-1 8:30' union allselect '李四',2,'2011-10-1 17:30' union allselect '王五',1,'2011-10-1 8:30' union allselect '张三',1,'2011-10-3 8:30' union allselect '张三',2,'2011-10-3 17:30' union allselect '张三',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_aaaCURSOR
DECLARE @DayIDNVARCHAR(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高手帮忙看看,该如何解决

热点推荐