asked on

Simple Pivot table in SQL Server 2005 Express

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

ralmada

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

panhead802

ASKER

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.

ralmada

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.

panhead802

ASKER

I am running in management studio express.

ralmada

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

panhead802

ASKER

I checked in the Database properties, Compatability level is set to SQL Server 2005(90).

Anything else to check?

ralmada

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

panhead802

ASKER

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..

panhead802

ASKER

Oops, my bad. It returns my table without the pivot...

panhead802

ASKER

Same error.

ASKER CERTIFIED SOLUTION

ralmada

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

panhead802

ASKER

Upgrade to SQL 2008 Resolved issue.