Pivot - 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

General SQL Server Forums

New to SQL Server Programming

Pivot

Author

Topic

chulheekim
Starting Member

46 Posts

Posted - 2008-12-05 : 12:16:22

I have a sql statement that pivots a table. I keep getting error saying 'Incorrect syntax near 'PIVOT''. I know my sql statement is legitimate because I copied it from a tutorial website. What do I do? it is sql server 2005 version.

SELECT PRODUCT, FRED, KATE
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product
) up
PIVOT (SUM(QTY) FOR CUST IN (FRED, KATE)) AS pvt
ORDER BY PRODUCT

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-05 : 12:18:13

[code]SELECT PRODUCT, FRED, KATE
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product
) up
PIVOT (SUM(QTY) FOR CUST IN ([FRED], [KATE])) AS pvt
ORDER BY PRODUCT
[/code]

chulheekim
Starting Member

46 Posts

Posted - 2008-12-05 : 13:06:53

I'm still getting the same error. Thank you anyway.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-05 : 13:12:01

whats your db's compatibility level? just run below and post back

EXEC sp_dbcmptlevel

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-12-05 : 13:15:04

are you sure its SQL 2005?

select @@version

chulheekim
Starting Member

46 Posts

Posted - 2008-12-05 : 13:21:57

This is what I got from sp_dbcmptlevel

Valid values of database compatibility level are 60, 65, 70, or 80.

What are these numbers?

chulheekim
Starting Member

46 Posts

Posted - 2008-12-05 : 13:24:54

wow that's frustrating. @@version says Microsoft SQL Server 2000. Trust me. I see 2005 on my Microsoft SQL server management Studio. How this could be possible?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-05 : 13:36:49

You are using SQL 2005 client tools to connect to SQL 2000 Server

60- SQL 6.0
65-SQL 6.5
70-SQL 7.0
80-SQL 2000
90-SQL 2005
......

chulheekim
Starting Member

46 Posts

Posted - 2008-12-05 : 13:37:46

Whatever it is I don't like using pivot and unpivot thing.
Do we have alternative for this below?

table for monthly sales per product
prodId yyyymm sales_total
------ ------ -----------
A 200707 3853.54
B 200707 1299.20
C 200707 832.69
D 200707 5323.10
A 200708 2992.90
B 200708 1509.67
C 200708 972.30
D 200708 4323.98
A 200709 2664.23
B 200709 1853.44
C 200709 821.21
D 200709 7743.60

I want this result from the sql query.
prodId 200707 200708 200709
------ ------ ------ ------
A 3853.54 2992.90 2664.23
B 1299.20 1509.67 1853.44
C 832.69 972.30 821.21
D 5323.10 4323.98 7743.60

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-05 : 13:42:42

select Prodid,
max(case when yyyymm = '200707' then sales_total else null end)as 200707,
max(case when yyyymm = '200708' then sales_total else null end)as 200708,
max(case when yyyymm = '200709' then sales_total else null end)as 200709
from table
group by prodid

Or use Dynamic cross tab.

chulheekim
Starting Member

46 Posts

Posted - 2008-12-05 : 13:54:13

Wow. That works great. I need to take a look at how it is done so easy though. Thank you very much.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-05 : 13:55:30

Cool

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-05 : 23:46:23

quote:
Originally posted by chulheekim

wow that's frustrating. @@version says Microsoft SQL Server 2000. Trust me. I see 2005 on my Microsoft SQL server management Studio. How this could be possible?

thats because your actual server you're connecting to is sql 2000 but you just used sql 2005 mgmnt studio (client tool) to connect to it.

so you cant use pivot in that case

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-05 : 23:48:56

quote:
Originally posted by sodeep

select Prodid,
max(case when yyyymm = '200707' then sales_total else null end)as 200707,
max(case when yyyymm = '200708' then sales_total else null end)as 200708,
max(case when yyyymm = '200709' then sales_total else null end)as 200709
from table
group by prodid

Or use Dynamic cross tab.

better to use sum() here rather than max just in case table contain more than 1 record for a single prodId for a particular yyyymm.
the sample data doesn't show any though.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-06 : 08:44:34

Yup.

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2008-12-12 : 05:05:07

select prodid,[200707] ,[200708],[200709]
from ( select * from @Temp )as p
pivot ( max( sales_total) for yyyymm in ( [200707],[200708], [200709] )) as d

I Struggle For Excellence

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-12 : 09:14:53

quote:
Originally posted by Nageswar9

select prodid,[200707] ,[200708],[200709]
from ( select * from @Temp )as p
pivot ( max( sales_total) for yyyymm in ( [200707],[200708], [200709] )) as d

I Struggle For Excellence

Did you have a habit of looking at what others have posted before? The pivot solution was given long back and op had told his db version is sql 2000 so it wont work

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-12-12 : 10:29:23

quote:

Originally posted by visakh16

SELECT PRODUCT, FRED, KATE
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product
) up
PIVOT (SUM(QTY) FOR CUST IN ([FRED], [KATE])) AS pvt
ORDER BY PRODUCT

visakh, what's the purpose of the brackets? (Sorry, no experience with PIVOT, yet!)

Terry

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-12 : 10:31:27

quote:
Originally posted by tosscrosby

quote:
Originally posted by visakh16

SELECT PRODUCT, FRED, KATE
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product
) up
PIVOT (SUM(QTY) FOR CUST IN ([FRED], [KATE])) AS pvt
ORDER BY PRODUCT

visakh, what's the purpose of the brackets? (Sorry, no experience with PIVOT, yet!)

Terry

its used as a escape character just in case value inside happens to be reserved word

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-15 : 06:18:21

Also useful when it has space

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources