Not getting desired result using PIVOT Operator

[Shweta]

Hi All,

I have a table 'LungCapData' with 726 rows. It has 6 columns named as 'LungCap', 'Age', 'Height', 'Smoke', 'Gender', 'Caesarean'.

Below is the query to get the age average for Male and Female for yes and no category of Caesarean.

select gender, yes, no
from lungcapdata
pivot
(
avg(age)
for caesarean 
in ([yes],[no])
)as pivot1

Below is the result I am getting using above query

gender	yes	no
female	3	NULL
female	NULL	3
female	NULL	4
male	NULL	3
female	NULL	5
female	NULL	3
male	NULL	6
female	NULL	8
male	NULL	3
female	NULL	7
female	NULL	8
male	8	NULL
male	NULL	5
male	NULL	7

<tbody>
</tbody>

My question is why is it not consolidating the results for gender. There should not be multiple results for male and female. I mean the result is not as good as we get in excel pivot tables like below

Row Labels no yes Grand Total female 12.39 12.67 12.45 male 12.02 12.81 12.21 Grand Total 12.20 12.74 12.33 <tbody> </tbody>

<tbody>
</tbody>

Kindly help.

Thanks in advance!

Regards,
Shweta Jain

 

[Shweta]

Got the desired result using below query

select Gender, [yes], [no]
from 
     (select Age,Gender,Caesarean
     from lungcapdata) as sourcetable
pivot
(
avg(Age)
for Caesarean 
in ([yes],[no])
)as pivot1

Thank you

 

[Shweta]

Sorry..I have a little issue with this result as well.

I want to keep the average of age upto 2 decimal points only. But when I try to round it up using round() or cast() function, get an error.

Please suggest.

 

[xenou]

Are you using SQL Server?

select Gender, round([yes],2) as [yes], round([no],2) as [no]
from 
     (select Age,Gender,Caesarean
     from #foo) as sourcetable
pivot
(
avg(Age)
for Caesarean 
in ([yes],[no])
)as pivot1

 

[Shweta]

Thanks Xenou..Yes, I am using SQL server. Let me try the one you suggested.

 

[Shweta]

Thanks Xenou...it's working fine for me.