Problem with Pivot Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problem with Pivot Table

Author

Topic

iwaters
Starting Member

13 Posts

Posted - 2003-01-28 : 12:13:43

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

7423 Posts

Posted - 2003-01-28 : 12:27:25

Just put what you have into a subquery, and query that but GROUP BY catName and IndicatorName, and SUM() up each column.

- Jeff

iwaters
Starting Member

13 Posts

Posted - 2003-01-28 : 12:40:40

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-28 : 13:01:33

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources