用个Join就可以搞定了

Create table T1
([TheDate] DateTime, [Value] int)

Go

insert into T1 values('2006-8-1', 100)
insert into T1 values('2006-8-2', 200)
insert into T1 values('2006-8-3', 300)
insert into T1 values('2006-8-5', 400)
insert into T1 values('2006-8-7', 220)
insert into T1 values('2006-8-8', 140)

Go

select A.TheDate, B.Value Today, C.Value Yesterday
from (select distinct TheDate from T1) A
left join (select TheDate, Value from T1) B
on A.TheDate=B.TheDate
left join (select dateadd(Day, 1, TheDate) as TheDate,Value from T1) C
on A.TheDate=C.TheDate

Go