Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

PIVOT function Expand / Collapse
Author
Message
Posted Sunday, March 14, 2010 5:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 12, 2011 7:01 PM
Points: 9, Visits: 102
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
Post #882643
Posted Sunday, March 14, 2010 5:53 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:18 AM
Points: 7,125, Visits: 13,173
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #882646
Posted Sunday, March 14, 2010 9:07 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:34 PM
Points: 36,952, Visits: 31,461
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #882668
Posted Monday, March 15, 2010 12:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 10:26 AM
Points: 11,194, Visits: 11,136
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #882728
Posted Thursday, March 18, 2010 9:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 12, 2011 7:01 PM
Points: 9, Visits: 102
Thanks for your help
Post #885664
Posted Thursday, March 18, 2010 9:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 12, 2011 7:01 PM
Points: 9, Visits: 102
Thanks for your help
Post #885666
Posted Thursday, April 7, 2011 3:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 12, 2014 7:25 AM
Points: 19, Visits: 227
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
Post #1089728
Posted Friday, April 8, 2011 4:50 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 8, 2014 8:04 AM
Points: 1,178, Visits: 2,648
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
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

Post #1090492
Posted Friday, April 8, 2011 6:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 12, 2014 7:25 AM
Points: 19, Visits: 227
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;
Post #1090544
Posted Friday, April 8, 2011 7:25 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:34 PM
Points: 36,952, Visits: 31,461
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1091016
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse