[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Simple Pivot table in SQL Server 2005 Express

Posted on 2010-09-02
12
Medium Priority
?
878 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
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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

650 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