Pivot Problem - SQL Server Forums

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

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Pivot Problem

Author

Topic

UnemployedInOz
Yak Posting Veteran

54 Posts

Posted - 2013-08-14 : 21:15:40

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.

UnemployedInOz
Yak Posting Veteran

54 Posts

Posted - 2013-08-14 : 21:42:23

Found the solution. I had to change the Compatibility level of the DB

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-16 : 13:49:32

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

john1234
Starting Member

1 Post

Posted - 2013-08-24 : 23:05:34

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources