| Author |
 Topic  |
|
|
iwaters
Starting Member
13 Posts |
 Posted - 01/28/2003 : 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
USA
7423 Posts |
Posted - 01/28/2003 : 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 - 01/28/2003 : 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
USA
7423 Posts |
Posted - 01/28/2003 : 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 |
|
|
| |
Topic |
|
|
|
Problem with Pivot Table
