Solved

Simple Pivot table in SQL Server 2005 Express

Posted on 2010-09-02
12
862 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
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
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…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

791 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