Solved

Simple Pivot table in SQL Server 2005 Express

Posted on 2010-09-02
12
864 Views
Last Modified: 2012-05-10
I have a simple table in my database that looks like this:
YEAR_ID | Year_Name | F_ABOVE | T_ABOVE | A_BASE | T_BELOW | F_BELOW
5            |2005            |2000        |3000         |2000      |5000         |7000
6            |2006            |3000        |5000         |2500      |7400         |8000
7            |2007            |2500        |6000         |3600      |2560         |2600
8            |2008            |2670        |5000         |5800      |6500         |5866
9            |2009            |5971        |5987         |5642      |4543         |5343

I need to create a veiw from this table call GP_BASE that looks like this:

ID            |2005  |2006  |2007  |2008  | 2009  |2010  |
F_ABOVE |2000  |3000  |2500
T_ABOVE |3000  |5000  |6000
A_BASE   |2000  |2500  |3600
T_BELOW|5000  |7400  |2560
F_BELOW|7000  |8000  |2600

With the rest of the data filled out as indicated in the original table.

I am using SQL Server 2005 Express and tried to get something to work as below:

SELECT     *
FROM         (SELECT     YEAR_ID, YEAR_NAME, TheData
                       FROM          GP_BASE_PIVOT(TheData FOR Item IN ([F_ABOVE], [T_ABOVE], [A_BASE], [T_BELOW], [F_BELOW])) unpvt) derived PIVOT (SUM(TheData)
                      FOR YEAR IN ([2005/2006], [2006/2007], [2007/2008], [2008/2009], [2009/2010], [2010/2011])) pvt

It doesn't seem to work. this is my first time playing with Pivot tables so any help would be great.

Thanks
Dan
0
Comment
Question by:panhead802
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
12 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 33590126
you can try the below:

select Type as ID, [2005], [2006], [2007], [2008], [2009]
from 
(
	select Year_ID, Year_Name, Amt, Type
	from (
	select 	Year_ID,
		Year_Name,
		F_ABOVE,
		T_ABOVE, 
		A_BASE,
		T_BELOW,
		F_BELOW
	from GP_BASE_PIVOT
	) o
	UNPIVOT (Amt for type in (F_ABOVE, T_ABOVE, A_BASE, T_BELOW, F_BELOW)) p
) t1
PIVOT (max(Amt) for Year_Name in ([2005], [2006], [2007], [2008], [2009])) as t2

Open in new window

0
 

Author Comment

by:panhead802
ID: 33590386
I get the error message The UNPIVOT construct or statement is not supported.

Then it returns a view that has alot of nulls. Basically a row and column for each of the results.

0
 
LVL 41

Expert Comment

by:ralmada
ID: 33590467
I guess you're trying to run this query in Visual Studio, not in Management Studio (SSMS). Can you please advise?
I would suggest you try running it in Management studio first.
 
0
Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

 

Author Comment

by:panhead802
ID: 33590475
I am running in management studio express.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 33590530
so maybe your compatibility level is not 90, run
EXEC sp_dbcmptlevel  'yourdatabasename'
and see what you get. It should be 90 to be able to run the UNPIVOT statement.
To change it to 90
EXEC sp_dbcmptlevel  'yourdatabasename', 90
0
 

Author Comment

by:panhead802
ID: 33590553
I checked in the Database properties, Compatability level is set to SQL Server 2005(90).

Anything else to check?
0
 
LVL 41

Expert Comment

by:ralmada
ID: 33590656
Try using the alias like below:
If not, please post the exact query you're running

select Type as ID, [2005], [2006], [2007], [2008], [2009]
from 
(
	select Year_ID, Year_Name, up.Amt, up.[Type]
	from (
	select 	Year_ID,
		Year_Name,
		F_ABOVE,
		T_ABOVE, 
		A_BASE,
		T_BELOW,
		F_BELOW
	from GP_BASE_PIVOT
	) o
	UNPIVOT (Amt for [type] in (F_ABOVE, T_ABOVE, A_BASE, T_BELOW, F_BELOW)) up
) t1
PIVOT (max(Amt) for Year_Name in ([2005], [2006], [2007], [2008], [2009])) as t2

Open in new window

0
 

Author Comment

by:panhead802
ID: 33590675
I have it working with this:

SELECT     YEAR_NAME, F_ABOVE, T_ABOVE, A_BASE, T_BELOW, F_BELOW
FROM         GP_BASE_PIVOT PIVOT (COUNT(YEAR_ID) FOR YEAR_ID IN ([5], [6], [7], [8], [9], [10])) p

It still pops up an error about the construct being not supproted but when I click Ok it seems to run correctly..
0
 

Author Comment

by:panhead802
ID: 33590700
Oops, my bad. It returns my table without the pivot...
0
 

Author Comment

by:panhead802
ID: 33590776
Same error.
0
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
ID: 33591336
That seems to be a problem with the SQL instance you have. Make sure you have the latest service pack and patches applied. or try to upgrade to SQL 2008 express
0
 

Author Closing Comment

by:panhead802
ID: 34513728
Upgrade to SQL 2008 Resolved issue.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
spx for moving values to new table 5 75
SQL Select - Finding chars in a column 2 71
SQL Backup skipping a few tables 7 58
Caste datetime 2 69
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question