Pivot Table

Question

Pivot Table

timothy.j.little

Valued Member

Points: 70
More actions
September 23, 2008 at 8:23 am

#193387

When i Parse this query it goes through , but when i execute it i get an error , don't know what is causing it any help would be appreciated.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'picked_up'.
SELECT c.picked_up FROM (SELECT ticket_type From bpset_tckts)o
PIVOT (count(ticket_type)FOR ticket_type IN([CTMS],[BWTS ESCALATION TRACKER],[EWTS ESCALATION TRACKER],[SERVICE DESK]))p
JOIN picked_up c ON p.ticket_type=c.ticket_type
Order by DATENAME(mm,c.picked_up)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gabriel P SSCarpal Tunnel Points: 4289 More actions · Answer 1

timothy.j.little (9/23/2008)
When i Parse this query it goes through , but when i execute it i get an error , don't know what is causing it any help would be appreciated.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'picked_up'.
SELECT c.picked_up FROM (SELECT ticket_type From bpset_tckts)o
PIVOT (count(ticket_type)FOR ticket_type IN([CTMS],[BWTS ESCALATION TRACKER],[EWTS ESCALATION TRACKER],[SERVICE DESK]))p
JOIN picked_up c ON p.ticket_type=c.ticket_type
Order by DATENAME(mm,c.picked_up)

I don't know why it's complaining about picked_up, but I do know your join statement won't work. The "p" table won't contain a field called "ticket_type" after you've pivoted it, it will only have fields [CTMS],[BWTS ESCALATION TRACKER],[EWTS ESCALATION TRACKER],[SERVICE DESK]

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 2

Hi,

On your join you are referencing a table called 'picked_up'

Make sure that it exists.

The error you are getting is because it can't find that object.

thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

timothy.j.little Valued Member Points: 70 More actions · Answer 3

lets say i run it like this now im getting this wonder if im missing something , thanks with the join

Msg 207, Level 16, State 1, Line 5

Invalid column name 'picked_up'.

SELECT * FROM (SELECT ticket_type From bpset_tckts)o

PIVOT (count(ticket_type)FOR ticket_type IN([CTMS],[BWTS ESCALATION TRACKER],[EWTS ESCALATION TRACKER],[SERVICE DESK]))p

order by datename(mm,picked_up)

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 4

HI there,

Similar problem in your order BY clause you are referencing a column called picked_up which doesn't exists in your pivot query.

You'll need to return that column in your pivot if you wish to use it.

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 5

HI,

I would also suggest reading this for future posts 🙂

http://www.sqlservercentral.com/articles/Best+Practices/61537/

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life