SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Problem with Pivot Table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

iwaters
Starting Member

13 Posts

Posted - 01/28/2003 :  12:13:43  Show Profile  Reply with Quote
I am trying to create a pivot table to get performance indicators for different arears. The code below works OK except that it doesn't group the inidcators together, so I get mutliple rows for each indicator. I have indcluded an example of the result set below.

QUERY
SELECT Category.CatName, Indicator.IndicatorName,
CASE Area.AreaName WHEN 'Area1' THEN StatsData.DataValue ELSE 0 END AS A1,
CASE Area.AreaName WHEN 'Area2' THEN StatsData.DataValue ELSE 0 END AS A2,
CASE Area.AreaName WHEN 'Area3' THEN StatsData.DataValue ELSE 0 END AS A3,
CASE Area.AreaName WHEN 'Area4' THEN StatsData.DataValue ELSE 0 END AS A4,
CASE Area.AreaName WHEN 'Area5' THEN StatsData.DataValue ELSE 0 END AS A5,
CASE Area.AreaName WHEN 'Area6' THEN StatsData.DataValue ELSE 0 END AS A6,
CASE Area.AreaName WHEN 'Area7' THEN StatsData.DataValue ELSE 0 END AS A7
FROM Category INNER JOIN Indicator ON Category.CatID = Indicator.IndicatorID LEFT OUTER JOIN StatsData ON Indicator.IndicatorID = StatsData.IndicatorID
LEFT OUTER JOIN Area ON Area.AreaID = StatsData.AreaID

Results
| CatName | IndicatorName | A1 | A2 | A3 | A4 | A5 | A6 | A7|
|---------|---------------|----|----|----|----|----|----|---|
| Cat1 | Indicator1 |20 |0 |0 |0 |0 |0 |0 |
| Cat1 | Indicator1 |0 |20 |0 |0 |0 |0 |0 |

etc.

I want the above results to be on the same row so I have a figure for each indicator and every area on one row. E.G.
| CatName | IndicatorName | A1 | A2 | A3 | A4 | A5 | A6 | A7|
|---------|---------------|----|----|----|----|----|----|---|
| Cat1 | Indicator1 |20 |20 |0 |0 |0 |0 |0 |

Does anyone have any ideas?




jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 01/28/2003 :  12:27:25  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Just put what you have into a subquery, and query that but GROUP BY catName and IndicatorName, and SUM() up each column.

- Jeff
Go to Top of Page

iwaters
Starting Member

13 Posts

Posted - 01/28/2003 :  12:40:40  Show Profile  Reply with Quote
I have tried the following but it comes up with errors along the lines of:

Column 'pivot.A1' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. etc


SELECT pivot.*, pivot.CatName, pivot.IndicatorName
FROM (SELECT Category.CatName, Indicator.IndicatorName,
SUM(CASE Area.AreaName WHEN 'Area1' THEN StatsData.DataValue ELSE 0 END) AS A1,
SUM(CASE Area.AreaName WHEN 'Area2' THEN StatsData.DataValue ELSE 0 END) AS A2,
SUM(CASE Area.AreaName WHEN 'Area3' THEN StatsData.DataValue ELSE 0 END) AS A3,
SUM(CASE Area.AreaName WHEN 'Area4' THEN StatsData.DataValue ELSE 0 END) AS A4,
SUM(CASE Area.AreaName WHEN 'Area5' THEN StatsData.DataValue ELSE 0 END) AS A5,
SUM(CASE Area.AreaName WHEN 'Area6' THEN StatsData.DataValue ELSE 0 END) AS A6,
SUM(CASE Area.AreaName WHEN 'Area7' THEN StatsData.DataValue ELSE 0 END) AS A7
FROM Category
INNER JOIN Indicator ON Category.CatID = Indicator.IndicatorID
LEFT OUTER JOIN StatsData ON Indicator.IndicatorID = StatsData.IndicatorID
LEFT OUTER JOIN Area ON Area.AreaID = StatsData.AreaID
) AS pivot
GROUP BY pivot.CatName, pivot.IndicatorName

Could you explain where I am going wrong. I am not very good with these types of queries.

Thanks

Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 01/28/2003 :  13:01:33  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
You can't select all columns from PIVOT -- only ones that you are GROUPING by or that you are using an aggregate function on. You also had the SUM's in the wrong place -- you need to move them outside of the subquery.

So, all you need to do is:

SELECT

pivot.CatName, pivot.IndicatorName, SUM(A1) as A1, SUM(A2) as A2, SUM(A3) as A3, SUM(A4) as A4, SUM(A5) as A5, SUM(A6) AS a6, sum(a7) as a7

FROM

(SELECT Category.CatName, Indicator.IndicatorName,
CASE Area.AreaName WHEN 'Area1' THEN StatsData.DataValue ELSE 0 END AS A1,
CASE Area.AreaName WHEN 'Area2' THEN StatsData.DataValue ELSE 0 END AS A2,
CASE Area.AreaName WHEN 'Area3' THEN StatsData.DataValue ELSE 0 END AS A3,
CASE Area.AreaName WHEN 'Area4' THEN StatsData.DataValue ELSE 0 END AS A4,
CASE Area.AreaName WHEN 'Area5' THEN StatsData.DataValue ELSE 0 END AS A5,
CASE Area.AreaName WHEN 'Area6' THEN StatsData.DataValue ELSE 0 END AS A6,
CASE Area.AreaName WHEN 'Area7' THEN StatsData.DataValue ELSE 0 END AS A7
FROM Category
INNER JOIN Indicator ON Category.CatID = Indicator.IndicatorID
LEFT OUTER JOIN StatsData ON Indicator.IndicatorID = StatsData.IndicatorID
LEFT OUTER JOIN Area ON Area.AreaID = StatsData.AreaID
)
AS pivot
GROUP BY
pivot.CatName, pivot.IndicatorName

- Jeff
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000