SQL Server Forums
Profile | Register | 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
 New Topic  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
52317 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  
 New 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.03 seconds. Powered By: Snitz Forums 2000