Solved

Simple Pivot table in SQL Server 2005 Express

Posted on 2010-09-02
12
857 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:panhead802
Comment Utility
I am running in management studio express.
0
 
LVL 41

Expert Comment

by:ralmada
Comment Utility
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
Comment Utility
I checked in the Database properties, Compatability level is set to SQL Server 2005(90).

Anything else to check?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 41

Expert Comment

by:ralmada
Comment Utility
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
Comment Utility
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
Comment Utility
Oops, my bad. It returns my table without the pivot...
0
 

Author Comment

by:panhead802
Comment Utility
Same error.
0
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
Comment Utility
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
Comment Utility
Upgrade to SQL 2008 Resolved issue.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now