Solved

Simple Pivot table in SQL Server 2005 Express

Posted on 2010-09-02
12
861 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Merige returns error code when updating 15 53
SQL Express connecting form remote error 26 7 44
Truncate vs Delete 63 105
SQL Server Insert where not exists 24 41
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
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…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

770 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