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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Pivot Problem
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

UnemployedInOz
Yak Posting Veteran

Australia
54 Posts

Posted - 08/14/2013 :  21:15:40  Show Profile  Reply with Quote
I have the following statement:

CREATE TABLE #Product(Cust VARCHAR(25), Product VARCHAR(20), QTY INT)
GO
-- Inserting Data into Table
INSERT INTO #Product(Cust, Product, QTY)
VALUES('KATE','VEG',2)
INSERT INTO #Product(Cust, Product, QTY)
VALUES('KATE','SODA',6)
INSERT INTO #Product(Cust, Product, QTY)
VALUES('KATE','MILK',1)
INSERT INTO #Product(Cust, Product, QTY)
VALUES('KATE','BEER',12)
INSERT INTO #Product(Cust, Product, QTY)
VALUES('FRED','MILK',3)
INSERT INTO #Product(Cust, Product, QTY)
VALUES('FRED','BEER',24)
INSERT INTO #Product(Cust, Product, QTY)
VALUES('KATE','VEG',3)
GO

-- Pivot Table ordered by #Product
SELECT Product, FRED, KATE
FROM #Product
PIVOT (SUM(QTY) FOR CUST IN (FRED, KATE)) AS pvt
ORDER BY Product
GO

I get the error
Incorrect syntax near ')'.

I have worked it out to be the SUM(QTY) line but cannot see the problem.

Edited by - UnemployedInOz on 08/14/2013 21:30:55

UnemployedInOz
Yak Posting Veteran

Australia
54 Posts

Posted - 08/14/2013 :  21:42:23  Show Profile  Reply with Quote
Found the solution. I had to change the Compatibility level of the DB
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 08/16/2013 :  13:49:32  Show Profile  Reply with Quote
Please ensure there are no breaking code present before you change the compatibility level.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

john1234
Starting Member

India
1 Posts

Posted - 08/24/2013 :  23:05:34  Show Profile  Reply with Quote
Hi use below syntax for pivot in sql
SELECT <non-pivoted column>,
[first pivoted column] AS <column name>,
[second pivoted column] AS <column name>,
.............................
FROM
(<SELECT query that produces the data>)
AS <alias for the source query>
PIVOT
(
<aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
IN ( [first pivoted column], [second pivoted column], .............................)
) AS <alias for the pivot table>
<optional ORDER BY clause>;

you can find more details on pivot in sql in this link http://blogfornet.com/2013/08/using-pivot-in-sql-server/
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000