PIVOT query !!!

Question

Post reply

PIVOT query !!!

Hemant Kumar

Ten Centuries

Points: 1063
More actions
October 29, 2007 at 4:01 am

#376540

Hi All,
When I tried to execute the following query (that PIVOT’s on the varchar column), I ‘m getting an error.
SELECT SCHOOLID, 'Elementary' As Elementary, 'SBA' As SBA, 'Secondary' As Secondary
FROM
(SELECT SCHOOLID, FUNDINGSOURCE, AMOUNT FROM PO_ITEMS) As P
PIVOT (
SUM(AMOUNT) FOR FUNDINGSOURCE IN ('Elementary', 'SBA', 'Secondary')
) AS X
Error I get is:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'Elementary'.
Please help me to achieve this :w00t:

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

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

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

Did you start with a "select * " to see what's the cause of this error ?

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 2

The pivoted values should always be enclosed with square braces..

PIVOT ( SUM(AMOUNT) FOR FUNDINGSOURCE IN ([Elementary], [SBA], [Secondary] ) ) AS X

--Ramesh

Hemant Kumar Ten Centuries Points: 1063 More actions · Answer 3

Hi Guys 🙂

Thanks for looking into my issue.

ALZDBA

Did you start with a "select * " to see what's the cause of this error ?

I execute my query using "select * " and still i get the same exception as posted first.

Ramesh

The pivoted values should always be enclosed with square braces

Those are values (Elementary, Secondary,..) for the column FundingSource. Still i tried your suggestion by removing the string delimiter and enclosed them within square brackets like:

SELECT SCHOOLID, 'Elementary' As Elementary, 'SBA' As SBA, 'Secondary' As Secondary FROM (SELECT SCHOOLID, FUNDINGSOURCE, AMOUNT FROM PO_ITEMS) As P PIVOT ( SUM(AMOUNT) FOR FUNDINGSOURCE IN ([Elementary], [SBA], [Secondary]) ) AS X

On executing the above query, i got the resultset as

SCHOOLID Elementary SBA Secondary
---------------------------------------------------
1 Elementary SBA Secondary
2 Elementary SBA Secondary
5 Elementary SBA Secondary

Then i executed the query as follows:

SELECT *

FROM (SELECT SCHOOLID, FUNDINGSOURCE, AMOUNT FROM PO_ITEMS) As P

PIVOT ( SUM(AMOUNT) FOR FUNDINGSOURCE IN ([Elementary], [SBA], [Secondary])

and the query returned the resultset as exptected :w00t:

Now, I 'm confused and have 2 questions here:

1. If i substitute SCHOOLID, 'Elementary' As Elementary, 'SBA' As SBA, 'Secondary' As Secondary instead of *, why am i getting error?

2. SUM(AMOUNT) FOR FUNDINGSOURCE IN ([Elementary], [SBA], [Secondary]

Here the Elementary, SBA, Secondary are actually the values for the column FUNDINGSOURCE and normally we used to surround them using quote character. How did sql accepted it the way we have defined between brackets instead of quotes?

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 4

Now, I 'm confused and have 2 questions here:
1. If i substitute SCHOOLID, 'Elementary' As Elementary, 'SBA' As SBA, 'Secondary' As Secondary instead of *, why am i getting error?

You cannot select columns (in this case: AMOUNT & FUNDINGSOURCE) that are part of a PIVOT Clause!!!!

2. SUM(AMOUNT) FOR FUNDINGSOURCE IN ([Elementary], [SBA], [Secondary]
Here the Elementary, SBA, Secondary are actually the values for the column FUNDINGSOURCE and normally we used to surround them using quote character. How did sql accepted it the way we have defined between brackets instead of quotes?

It is part of the syntax....

--Ramesh

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

you should be also skipping the quotes in the main select part, i.e.

SELECT SCHOOLID, [Elementary] As Elementary, [SBA] As SBA, [Secondary] As Secondary FROM

(SELECT SCHOOLID, FUNDINGSOURCE, AMOUNT FROM PO_ITEMS) As P

PIVOT (

SUM(AMOUNT) FOR FUNDINGSOURCE IN ([Elementary], [SBA], [Secondary]) ) AS X

Remember - the "purpose" of pivot is to use the values in a given column as new column names, which is why you're using the brackets notation, instead of the quotes (meaning a string literal).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Hemant Kumar Ten Centuries Points: 1063 More actions · Answer 6

Thanks Ramesh & Matt & All :cool:,

That helped me to understand about the pivot operator and now I 'm clear 🙂