--
测试数据
declare @T table (yd varchar ( 10 ),rs int )
insert @T
select ' 2006-1 ' , 5 union all
select ' 2006-2 ' , 8 union all
select ' 2006-5 ' , 10 union all
select ' 2006-8 ' , 3 union all
select ' 2006-12 ' , 10
select top 12 mon = identity ( int , 1 , 1 ) into #mon from syscolumns
select yd = a.yd + ltrim (b.mon),rs = isnull (c.rs, 0 ) from
( select distinct yd =left (yd, 5 ) from @T ) a
cross join #mon b
left join @T c
on a.yd + ltrim (b.mon) = c.yd
/*
yd rs
2006-1 5
2006-2 8
2006-3 0
2006-4 0
2006-5 10
2006-6 0
2006-7 0
2006-8 3
2006-9 0
2006-10 0
2006-11 0
2006-12 10
*/
-- 删除测试
drop table #mon
declare @T table (yd varchar ( 10 ),rs int )
insert @T
select ' 2006-1 ' , 5 union all
select ' 2006-2 ' , 8 union all
select ' 2006-5 ' , 10 union all
select ' 2006-8 ' , 3 union all
select ' 2006-12 ' , 10
select top 12 mon = identity ( int , 1 , 1 ) into #mon from syscolumns
select yd = a.yd + ltrim (b.mon),rs = isnull (c.rs, 0 ) from
( select distinct yd =left (yd, 5 ) from @T ) a
cross join #mon b
left join @T c
on a.yd + ltrim (b.mon) = c.yd
/*
yd rs
2006-1 5
2006-2 8
2006-3 0
2006-4 0
2006-5 10
2006-6 0
2006-7 0
2006-8 3
2006-9 0
2006-10 0
2006-11 0
2006-12 10
*/
-- 删除测试
drop table #mon