Pivot error... puzzling - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Pivot error... puzzling

Author

Topic

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2012-07-20 : 09:24:11

Hello there. I have the following pivot script below.

it looks fine to me but im getting an error regarding a column name but cannot see where iam going wrong.

Select LOOKUP_FULL_DESC, [Affiliate], [Associate],[Member], [Certified],[Fellow]
from

( select lookup_Class.LOOKUP_FULL_DESC as 'class', (mem.member_ref)
individual ind WITH (NOLOCK) , member mem,
attribute attribute1 WITH (NOLOCK) ,
membership_history memHist WITH (NOLOCK) , lookup lookup,
lookup lookup_class,
Membership_grade grade,
membership_plan memPlan WITH (NOLOCK) , member mem2 WITH (NOLOCK)
where mem.join_date < (SELECT DATEADD(month,datediff(month,-0,getdate())-0,+1))
and mem.individual_ref = ind.individual_ref
and mem.member_class = lookup_class.lookup_ref
and UPPER(attribute1.attribute_code) =
(select UPPER((SELECT convert(varchar(3),datename(month,DATEADD(month,datediff(month,-0,getdate())+2,-0))))))
and attribute1.individual_ref = ind.individual_ref
and mem.leave_date is null
and memPlan.member_plan_ref = memHist.member_plan_ref
and memHist.valid_from = (SELECT DATEADD(month,datediff(month,-0,getdate())+2,-0))
and memHist.HISTORY_STATUS = lookup.lookup_ref
--and grade.MEMBER_GRADE_REF = mem2.MEMBER_GRADE
and (memhist.member_ref = mem2.member_ref
or memHist.feepaying_member = mem2.member_ref)
and mem2.individual_ref = ind.individual_ref
--group by Lookup.LOOKUP_FULL_DESC

) AS sources
pivot
(
count(member_ref) for class
in ([Affiliate], [Associate],[Member], [Certified],[Fellow] )
)
as piv
----------------------------------------------------------------
error

Msg 207, Level 16, State 1, Line 1
Invalid column name 'LOOKUP_FULL_DESC'.

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2012-07-20 : 10:03:31

ok its ok i cracked it.

I needed to create a secondary join for the lookup_full_desc field.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-20 : 10:08:35

was that the working code you posted? i dont think thats syntactically correct

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2012-07-20 : 12:52:35

no that code wasn't working. but I managed to sort it. I was referencing
the pivot values from the same column within the select statement.
so I has to create another alias.

thank you anyway.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-20 : 14:10:01

ok...just checked...thanks for confirming

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources