How to get this out put ?

Hi everybody I have a table call Child
and the information like this

ChildId Department Born_Dttm
01 ICU 01/01/2007
02 NormatWard 01/01/2006
03 ICU 01/01/2005

I need to get this out put like i need to count how many child attached to a particular department depending on child age ?

Department < 5 Months > 5Months 1 Year 2Year
ICU 1 0 0 1
NormatWard 0 0 1 0

Age calculation should be up to todays date ?

Any Idea ?
regards
suis

Here are a couple of resources detailing how to do this.

Pivot Tables - How to rotate a table in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;175574

Pivot Tables -Dynamic Cross-Tabs
http://www.sqlteam.com/item.asp?ItemID=2955

|||Hi Arni
thank you for u r quick response and many thanks to u r link
actually i have learned some thing out of that link and i could produce some SQL
Bellow is my SQL

DECLARE @.Child TABLE

(

[ChildId] varchar(2),

[Department] varchar(10),

[Born_Dttm] varchar(12)

)

INSERT INTO @.Child([ChildId], [Department], [Born_Dttm])

SELECT '01', 'ICU', '01/01/2007' UNION ALL

SELECT '02', 'NormalWard', '01/01/2006' UNION ALL

SELECT '03', 'ICU', '01/01/2005'

select [Department] ,

(select count(datediff(mm,[Born_Dttm],getdate())/12) from @.Child ch1 where datediff(mm,[Born_Dttm],getdate())/12 < 1 AND ch1.[Department]=ch.[Department] ) as ZeroToOneYear,
(select count(datediff(mm,[Born_Dttm],getdate())/12) from @.Child ch2 where datediff(mm,[Born_Dttm],getdate())/12 >= 1 AND datediff(mm,[Born_Dttm],getdate())/12 < 2 AND ch2.[Department]=ch.[Department] ) as OneTOTwoYear,
(select count(datediff(mm,[Born_Dttm],getdate())/12) from @.Child ch2 where datediff(mm,[Born_Dttm],getdate())/12 >= 2 AND datediff(mm,[Born_Dttm],getdate())/12 < 3 AND ch2.[Department]=ch.[Department] ) as TwoTOThreeYear
from @.Child ch
GROUP BY ch.[Department]

Now the problem is i can't get the Months calculation ?
can u help me to get months calculation
like 0 to 5 months ? and 5 to 1 year ?
Any Idea ?
regards
suis|||

This might work more efficiently. (Note the ZeroToOneYear is double counting the Months columns.)

Code Snippet

DECLARE @.Child TABLE
( [ChildId] varchar(2),
[Department] varchar(10),
[Born_Dttm] varchar(12)
)

INSERT INTO @.Child VALUES ( '01', 'ICU', '01/01/2007' )
INSERT INTO @.Child VALUES ( '02', 'NormalWard', '01/01/2006' )
INSERT INTO @.Child VALUES ( '03', 'ICU', '01/01/2005' )

SELECT
[Department],
[Under 5 Months] = sum( CASE WHEN datediff( month, Born_Dttm, getdate() ) < 5 THEN 1 ELSE 0 END ),
[5 MonthsToOneYear] = sum( CASE WHEN datediff( month, Born_Dttm, getdate() ) BETWEEN 5 AND 12 THEN 1 ELSE 0 END ),
[ZeroToOneYear] = sum( CASE datediff( year, Born_Dttm, getdate() ) WHEN 0 THEN 1 ELSE 0 END ),
[OneToTwoYear] = sum( CASE datediff( year, Born_Dttm, getdate() ) WHEN 1 THEN 1 ELSE 0 END ),
[TwoTOThreeYear] = sum( CASE datediff( year, Born_Dttm, getdate() ) WHEN 2 THEN 1 ELSE 0 END )
from @.Child ch
GROUP BY ch.[Department]

Department Under 5 Months 5 MonthsToOneYear ZeroToOneYear OneToTwoYear TwoTOThreeYear
- -- -- - --
ICU 1 0 1 0 1
NormalWard 0 0 0 1 0

|||Try this:

select [Department],

sum(case when age_mos < 5 then 1 else 0 end) as '< 5 months',

sum(case when age_mos > 4 and age_mos < 12 then 1 else 0 end) as '> 5 months',

sum(case when age_mos > 11 and age_mos < 24 then 1 else 0 end) as '1 year',

sum(case when age_mos > 23 then 1 else 0 end) as '2 year'

from

(select [Department], datediff(mm, [Born_Dttm], getdate()) as age_mos

from @.child) as t

group by [Department]

|||Thanks everybody for this valuable comments
now i could manage to sort out with the help of this forum
this is great help for me,
and this is the place to learn ........

many thanks.......to MSDN forum members ..........

regards
suis