How to get this out put ?

Hi everybody
I have a requirement like this
I have a table call Child which looks like following

ChildId ChildAgeByYear ReferralSoure NoOfReferral
Ch01 02 Self 2
Ch01 02 Open 1
Ch03 02 Self 1

Now my problem is I need to display the above Child table information like this

AgeGroup ReferralSoure NoOfReferral
0 0
1 0
2 Self 3
Open 1
3 0
4 0

I need to count the child age group by child age like, 1, 2, 3, 4, and their ReferralSource and NoOfReferral for each child depends on the ReferralSource

Any Idea ?

Regards
Suis

Perhaps something like this:

declare @.child table
( ChildId varchar(5),
ChildAgeByYear integer,
ReferralSource varchar(12),
NoOfReferral integer
)
insert into @.child
select 'Ch01', 2, 'Self', 2 union all
select 'Ch01', 2, 'Open', 1 union all
select 'Ch03', 2, 'Self', 1
--select * from @.child

select AgeGroup,
isnull(ReferralSource, '') as ReferralSource,
sum( case when NoOfReferral is null then 0
else NoOfReferral end
) as NoOfReferral
from ( select 0 as AgeGroup union all select 1 union all
select 2 union all select 3 union all select 4
) as n
left join @.child c
on n.AgeGroup = c.ChildAgeByYear
group by AgeGroup,
ReferralSource
order by AgeGroup,
sum( case when NoOfReferral is null then 0
else NoOfReferral end
) desc

/*
AgeGroup ReferralSource NoOfReferral
-- --
0 0
1 0
2 Self 3
2 Open 1
3 0
4 0
*/

|||Thank you very much
4 your quick response,i will let u now the outcome once i test this
best regards
suis