select a.name1 as class,ISnull(a.[2012-02-01],'')+c.[2012-02-01] as [2012-02-01],ISnull(b.[2012-02-02],'')+c.[2012-02-02] as [2012-02-02],ISnull(a.[2012-02-03],'')+ISnull(b.[2012-02-03],'') as [2012-02-03] from (select * from test1 Pivot( max(content1) for id1 in ([2012-02-01],[2012-02-03]) ) as pvt) a, (select * from test2 Pivot( max(content2) for id2 in ([2012-02-02],[2012-02-03]) ) as pvt) b, (select * from test3 Pivot( max(content3) for id3 in ([2012-02-01],[2012-02-02]) ) as pvt) c where a.name1 = b.name2
select a.name1 as class,ISnull(a.[2012-02-01],'')+c.[2012-02-01] as [2012-02-01],ISnull(b.[2012-02-02],'')+c.[2012-02-02] as [2012-02-02],ISnull(a.[2012-02-03],'')+ISnull(b.[2012-02-03],'') as [2012-02-03] from
(select * from test1
Pivot(
max(content1)
for id1 in
([2012-02-01],[2012-02-03])
)
as pvt) a,
(select * from test2
Pivot(
max(content2)
for id2 in
([2012-02-02],[2012-02-03])
)
as pvt) b,
(select * from test3
Pivot(
max(content3)
for id3 in
([2012-02-01],[2012-02-02])
)
as pvt) c
where a.name1 = b.name2