October 13, 2009 at 2:46 am
Hi
can someone please tell me what is wrong with this query
select ee_occup_levels, gender, race
from (select ee_occup_levels, gender, race
from dtiheadcount inner join reportingstructure
on dtiheadcount.costcenter = reportingstructure.costcenter
where (period = 200909)
and (dtiheadcount.headcount = 'headcount')
and (dtiheadcount.hccat2 = 'permanent')
and (dtiheadcount.staffno is not null)) AS x
Pivot (Count(race) for gender in ('m', 'f')) as pvt
when I run it, I get syntax error near 'm'
Thanks
October 13, 2009 at 3:20 am
thatok (10/13/2009)
Hican someone please tell me what is wrong with this query
select ee_occup_levels, gender, racefrom (select ee_occup_levels, gender, race
from dtiheadcount inner join reportingstructure
on dtiheadcount.costcenter = reportingstructure.costcenter
where (period = 200909)
and (dtiheadcount.headcount = 'headcount')
and (dtiheadcount.hccat2 = 'permanent')
and (dtiheadcount.staffno is not null)) AS x
Pivot (Count(race) for gender in ('m', 'f')) as pvt
when I run it, I get syntax error near 'm'
Thanks
As per BOL,
FOR
[<column that contains the values that will become column headers>]
IN ( [first pivoted column] , [second pivoted column] ,
... [last pivoted column] )
So, should not it be?
Pivot (Count(race) for gender in ([m], [f])) as pvt
and those two columns should be in select clause!
hope that helps!
---------------------------------------------------------------------------------
October 13, 2009 at 3:32 am
Hi
They are included in the select, but now I get an error that say invalid column names when I use brackets
October 13, 2009 at 3:39 am
How about this?
select ee_occup_levels, [1] as M, [2] as F
from (select ee_occup_levels, gender, race
from dtiheadcount inner join reportingstructure
on dtiheadcount.costcenter = reportingstructure.costcenter
where (period = 200909)
and (dtiheadcount.headcount = 'headcount')
and (dtiheadcount.hccat2 = 'permanent')
and (dtiheadcount.staffno is not null)) AS x
Pivot (Count(race) for gender in ([1], [2])) as pvt
---------------------------------------------------------------------------------
October 13, 2009 at 3:44 am
It runs but does not count anything
October 13, 2009 at 3:55 am
Hi,
What PP-564103 meant when he said "those two columns should be in select clause!" is that the NEW columns should be in the select like this:
select ee_occup_levels, m, f
from (select ee_occup_levels, gender, race
from dtiheadcount inner join reportingstructure
on dtiheadcount.costcenter = reportingstructure.costcenter
where (period = 200909)
and (dtiheadcount.headcount = 'headcount')
and (dtiheadcount.hccat2 = 'permanent')
and (dtiheadcount.staffno is not null)) AS x
Pivot (Count(race) for gender in ([m], [f])) as pvt
PP was quicker
October 13, 2009 at 3:58 am
Beautiful, Thanks a lot to both of you
October 13, 2009 at 4:03 am
PP was quicker
Thanks:)
thatok (10/13/2009)
Beautiful, Thanks a lot to both of you
you're welcome 🙂
---------------------------------------------------------------------------------
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply