Count of patients department wise in SQL Server -

select distinct     patient_ref_master.dept_id 'dept',     patient_ref_master.male_femal 'gender',     count(patient_ref_master.pat_id) 'count'      patient_ref_master left join      patient_master on patient_master.pat_code=patient_ref_master.pat_id      (patient_ref_master.age > 16       , dbo.patient_master.pat_sex = 2       , patient_ref_master.creation_date = '2013/08/02')      or      (patient_ref_master.age > 16       , dbo.patient_master.pat_sex = 1       , patient_ref_master.creation_date = '2013/08/02')      or     (patient_ref_master.age >= 0       , patient_ref_master.age <= 16       , dbo.patient_master.pat_sex = 2       , patient_ref_master.creation_date = '2013/08/02')      or     (patient_ref_master.age >= 0       , patient_ref_master.age <= 16       , dbo.patient_master.pat_sex = 1       , patient_ref_master.creation_date = '2013/08/02')  group      patient_ref_master.male_femal, patient_ref_master.dept_id 

above query returns me table follows

  dept    gender           count     102    females   3     102    males     4     103    boys          2     103    females   2     103    girls     1     103    males     1     104    females   6     104    males     1 

here getting count of males,females,girls , boys according department. want output displayed in following manner

 dept    males females boys girls      102    3        2     5     5       103    4        5     2     6        104    2        1     1     5 

that department-wise count of boys, girls, males , females. have above pattern? pivot option it? have never used pivot.

please help. thanks

;with mycte (     select    distinct               patient_ref_master.dept_id 'dept',               patient_ref_master.male_femal 'gender',               count(patient_ref_master.pat_id) 'count'          patient_ref_master               left join patient_master                    on patient_master.pat_code = patient_ref_master.pat_id        (                   patient_ref_master.age > 16                    , dbo.patient_master.pat_sex = 2                    , patient_ref_master.creation_date = '2013/08/02'              )              or              (                   patient_ref_master.age > 16                    , dbo.patient_master.pat_sex = 1                    , patient_ref_master.creation_date = '2013/08/02'              )              or              (                   patient_ref_master.age >= 0                    , patient_ref_master.age <= 16                    , dbo.patient_master.pat_sex = 2                    , patient_ref_master.creation_date = '2013/08/02'              )              or              (                   patient_ref_master.age >= 0                    , patient_ref_master.age <= 16                    , dbo.patient_master.pat_sex = 1                    , patient_ref_master.creation_date = '2013/08/02'              )     group patient_ref_master.male_femal,              patient_ref_master.dept_id ) select     dept,            [males],             [females],             [boys],             [girls]       (                select gender,                        count ,                       dept                  mycte            ) sourcetable pivot (     max(count)     gender in ([males], [females], [boys], [girls]) ) pivottable 

the mycte as... block defines common table expression. mean of defining view on fly. in case use in order make code more readable. may find more info on using common table expressions

then pivot code way of transposing table rows columns, in fact asked for. have omitted cte part , in clause of pivot add piece of code, unreadable. more info on pivot in using pivot , unpivot

unfortunately way pivot works, can define 3 columns in there

1. one "id" of result table
2. one contain values want present rows
3. and 1 contain aggregate need have

so pivot , nothing else. pivot still table , may join other tables.

as addition have make note may define more 1 ctes each 1 containing references on previous defined. syntax is:

;with cte1 (   cte1 select ) , cte2 (   cte2 select ... may refer cte1 ) 

so, complete answer like:

;with mycte (     select    distinct               patient_ref_master.dept_id 'dept',               patient_ref_master.male_femal 'gender',               count(patient_ref_master.pat_id) 'count'          patient_ref_master               left join patient_master                    on patient_master.pat_code = patient_ref_master.pat_id        (                   patient_ref_master.age > 16                    , dbo.patient_master.pat_sex = 2                    , patient_ref_master.creation_date = '2013/08/02'              )              or              (                   patient_ref_master.age > 16                    , dbo.patient_master.pat_sex = 1                    , patient_ref_master.creation_date = '2013/08/02'              )              or              (                   patient_ref_master.age >= 0                    , patient_ref_master.age <= 16                    , dbo.patient_master.pat_sex = 2                    , patient_ref_master.creation_date = '2013/08/02'              )              or              (                   patient_ref_master.age >= 0                    , patient_ref_master.age <= 16                    , dbo.patient_master.pat_sex = 1                    , patient_ref_master.creation_date = '2013/08/02'              )     group patient_ref_master.male_femal,              patient_ref_master.dept_id ) ,pivotcte (     select     dept,                [males],                 [females],                 [boys],                 [girls]           (                    select gender,                            count ,                           dept                      mycte                ) sourcetable     pivot     (         max(count)         gender in ([males], [females], [boys], [girls])     ) pivottable ) select    dept,           deptname           [males],            [females],            [boys],            [girls]      pivotcte p           join departmenttable d                on p.dept = d.dept