Struggling with a 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

Struggling with a PIVOT

Author

Topic

linker3000
Starting Member

2 Posts

Posted - 2010-07-19 : 06:46:58

Hi,

I am playing with PIVOT for the first time on a very simple table on SQL Server 2005 (Standard) but am failing miserably!!

The table is a list of sites and price deviations eg:


Product_ID  Site_ID  Price
ABC1           1     10.40
ABC1           2     11.20
XYZ1           1     20.30
XYZ1           2     19.49
XYZ1           3     21.02

And all I want to do is produce a summary of stock items and the price deviation per site - eg:


Product Site1 Site2 Site3...
ABC1     10.40 11.20 
XYZ1     20.30 19.49 21.02

The query I have written excludes 'hidden' items and looks like this:


select *
from (
SELECT CodeId
      ,IsHidden
      ,SiteId
      ,PricePerPack
  FROM database.pricetable
  where isHidden = 0
 ) as D
 PIVOT
 (
  PricePerPack

 ) as P

But I just get:


Msg 102, Level 15, State 1, Line 14
Incorrect syntax near ')'.

I presume I am missing the point on something obvious but it escapes me at the moment so a shove in the right direction would be appreciated.

Thanks

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-07-19 : 06:51:51

I have a blog article to maybe assist you with your question:

http://www.sql-programmers.com/Blog/tabid/153/EntryId/6/Using-PIVOT-and-UNPIVOT.aspx

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-07-19 : 07:00:33

Try this,

select *
from (
SELECT Product_ID
,Site_ID
,Price
FROM pricetable
) as D
PIVOT
(
sum(Price)
For Site_ID in
([1],[2],[3])
) as P

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

linker3000
Starting Member

2 Posts

Posted - 2010-07-19 : 07:10:12

Thanks for the quick reply but I have had a read of the blog post and I am none the wiser as I do not want to do any SUMming, just find all the price deviations for each product and display them in a row.

Guess I picked the wrong week to turn my hand to T-SQL and also some object-oriented coding - I'd rather go back to assembler, it was much easier!

Edit: looks like our last two posts crossed - Thanks for the code example; with the FOR statement in place everything springs into life as expected so many thanks!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-19 : 09:07:34

For dynamic values,refer
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources