PIVOT function

Question

PIVOT function

muratistanbul

SSC Veteran

Points: 267
More actions
March 14, 2010 at 5:40 pm

#217791

Hi, When I use PIVOT function , ALL THE TIMES I GET
Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near 'PIVOT'.
Message is anybody have suggestion for me.
Thanks in advance

Viewing 15 posts - 1 through 15 (of 16 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

muratistanbul (3/14/2010)
Hi, When I use PIVOT function , ALL THE TIMES I GET
Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near 'PIVOT'.
Message is anybody have suggestion for me.
Thanks in advance

It's exactly what it says: The syntax you're using is wrong.

If you'd actually post the query you currently have we might be able to help you with a more precise answer than "correct the syntax".

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

Are you using SQL Server 2005 or better? If not, you'll need to use a cross-tab, instead.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 3

Simple demo:

DECLARE @demo

TABLE (

group_id INTEGER NOT NULL,

value MONEY NOT NULL

);

INSERT @demo (group_id, value) VALUES (1, $1.00);

INSERT @demo (group_id, value) VALUES (1, $2.00);

INSERT @demo (group_id, value) VALUES (1, $3.00);

INSERT @demo (group_id, value) VALUES (1, $4.00);

INSERT @demo (group_id, value) VALUES (2, $5.00);

INSERT @demo (group_id, value) VALUES (2, $4.00);

INSERT @demo (group_id, value) VALUES (3, $3.00);

INSERT @demo (group_id, value) VALUES (3, $2.00);

INSERT @demo (group_id, value) VALUES (3, $7.00);

INSERT @demo (group_id, value) VALUES (4, $2.00);

SELECT group_id,

value

FROM @demo;

SELECT P.[1], P.[2], P.[3], P.[4]

FROM @demo D

PIVOT (

SUM(D.value) FOR

D.group_id IN ([1], [2], [3], [4])

) P;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

muratistanbul SSC Veteran Points: 267 More actions · Answer 4

muratistanbul

SSC Veteran

Points: 267

March 18, 2010 at 9:51 am

#1136090

Thanks for your help

muratistanbul SSC Veteran Points: 267 More actions · Answer 5

muratistanbul

SSC Veteran

Points: 267

March 18, 2010 at 9:52 am

#1136092

Thanks for your help

vilonel Old Hand Points: 357 More actions · Answer 6

Hi,

Newbie here - I am trying to run the simple pivot demo as on one of the above post (copy and paste) and I am getting the error:

Msg 170, Level 15, State 1, Line 25

Line 25: Incorrect syntax near 'PIVOT'.

on SQL 2008

Please help - thanks

nigel. SSChampion Points: 11636 More actions · Answer 7

Hi,

Seems to work ok for me.

Try removing whitespace after pasting the code, occasionally I've found odd (non-display) characters in code pasted from the forums.

Nigel
How to post questions

vilonel Old Hand Points: 357 More actions · Answer 8

I have removed the "big spaces" and PF5 the sql qry but still get the Incorrect syntax near 'PIVOT' error - do you think it could be my SQL thats messing around or am I missing something in the below sql code - thanks

DECLARE @demo

TABLE (

group_id INTEGER NOT NULL,

value MONEY NOT NULL

);

INSERT @demo (group_id, value) VALUES (1, $1.00);

INSERT @demo (group_id, value) VALUES (1, $2.00);

INSERT @demo (group_id, value) VALUES (1, $3.00);

INSERT @demo (group_id, value) VALUES (1, $4.00);

INSERT @demo (group_id, value) VALUES (2, $5.00);

INSERT @demo (group_id, value) VALUES (2, $4.00);

INSERT @demo (group_id, value) VALUES (3, $3.00);

INSERT @demo (group_id, value) VALUES (3, $2.00);

INSERT @demo (group_id, value) VALUES (3, $7.00);

INSERT @demo (group_id, value) VALUES (4, $2.00);

SELECT group_id,

value

FROM @demo;

SELECT P.[1], P.[2], P.[3], P.[4]

FROM @demo D PIVOT(SUM(D.value) FOR D.group_id IN ([1], [2], [3], [4])) P;

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

vilonel (4/8/2011)
I have removed the "big spaces" and PF5 the sql qry but still get the Incorrect syntax near 'PIVOT' error - do you think it could be my SQL thats messing around or am I missing something in the below sql code - thanks
DECLARE @demo
TABLE (
group_id INTEGER NOT NULL,
value MONEY NOT NULL
);
INSERT @demo (group_id, value) VALUES (1, $1.00);
INSERT @demo (group_id, value) VALUES (1, $2.00);
INSERT @demo (group_id, value) VALUES (1, $3.00);
INSERT @demo (group_id, value) VALUES (1, $4.00);
INSERT @demo (group_id, value) VALUES (2, $5.00);
INSERT @demo (group_id, value) VALUES (2, $4.00);
INSERT @demo (group_id, value) VALUES (3, $3.00);
INSERT @demo (group_id, value) VALUES (3, $2.00);
INSERT @demo (group_id, value) VALUES (3, $7.00);
INSERT @demo (group_id, value) VALUES (4, $2.00);
SELECT group_id,
value
FROM @demo;
SELECT P.[1], P.[2], P.[3], P.[4]
FROM @demo D PIVOT(SUM(D.value) FOR D.group_id IN ([1], [2], [3], [4])) P;

Which version of SQL Server are you using?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

nigel. SSChampion Points: 11636 More actions · Answer 10

Jeff Moden (4/8/2011)
Which version of SQL Server are you using?

Ah-ha! Good point Jeff. But he did mention 2008 in an earlier post.

Nigel
How to post questions

vilonel Old Hand Points: 357 More actions · Answer 11

Using SQL 2008 - copied this info from SQL "about" form:

Microsoft SQL Server Management Studio 10.0.4000.0

Microsoft Analysis Services Client Tools 10.0.4000.0

Microsoft Data Access Components (MDAC) 6.1.7600.16385

Microsoft MSXML 3.0 4.0 5.0 6.0

Microsoft Internet Explorer 8.0.7600.16385

Microsoft .NET Framework 2.0.50727.4952

Operating System 6.1.7600

Is the problem with my SQL install - must I re-install my SQL 2008?

Thanks for the assistance

nigel. SSChampion Points: 11636 More actions · Answer 12

That only tells you the versions of the client tools etc installed on your machine, not the version of the database engine.

Run this in a query window:

SELECT @@VERSION

Nigel
How to post questions

vilonel Old Hand Points: 357 More actions · Answer 13

nigel. (4/11/2011)
That only tells you the versions of the client tools etc installed on your machine, not the version of the database engine.
Run this in a query window:
SELECT @@VERSION

will do and post asap

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 14

vilonel (4/11/2011)
nigel. (4/11/2011)
That only tells you the versions of the client tools etc installed on your machine, not the version of the database engine.
Run this in a query window:
SELECT @@VERSION
will do and post asap

While you're there, check the compatibility mode setting.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)