将行转换为报表的列

Using instructions found here I've tried to create a crosstab query to show historical data from three previous years and I would like to output it in a report.

使用此处的说明,我尝试创建一个交叉表查询,以显示前三年的历史数据,我想在报告中输出它。

I've got a few complications that are making this difficult and I'm having trouble getting the data to show correctly.

我有一些复杂性使得这很困难,我无法正确显示数据。

The query it is based on is structured like this:

它所基于的查询结构如下:

EmpID | ReviewYearID | YearName | ReviewDate  | SelfRating | ManagerRating | NotSelfRating |
  1   |      5       |   2013   | 01/09/2013  |    3.5     |      3.5      |      3.5      |
  1   |      6       |   2014   | 01/09/2014  |    2.5     |      2.5      |      2.5      |
  1   |      7       |   2015   | 01/09/2015  |    4.5     |      4.5      |      4.5      |
  2   |      6       |   2014   | 01/09/2014  |    2.0     |      2.0      |      2.0      |
  2   |      7       |   2015   | 01/09/2015  |    2.0     |      2.0      |      2.0      |
  3   |      7       |   2015   | 01/09/2015  |    5.0     |      5.0      |      5.0      |

[Edit]: Here is the SQL for the base query. It is combining data from two tables:

[编辑]:这是基本查询的SQL。它结合了两个表的数据:

SELECT tblEmployeeYear.EmployeeID AS EmpID, tblReviewYear.ID AS ReviewYearID, tblReviewYear.YearName, tblReviewYear.ReviewDate, tblEmployeeYear.SelfRating, tblEmployeeYear.ManagerRating, tblEmployeeYear.NotSelfRating
FROM tblReviewYear INNER JOIN tblEmployeeYear ON tblReviewYear.ID = tblEmployeeYear.ReviewYearID;

[/Edit]

I would like a crosstab query that transposes the columns/rows to show historical data for up to 3 previous years (based on review date) for a specific employee. The end result would look something like this for Employee ID 1:

我想要一个交叉表查询,它可以转换列/行,以显示特定员工最多3年前的历史数据(基于审核日期)。对于员工ID 1,最终结果看起来像这样:

Year          |  2015  |  2014  |  2013  |
SelfRating    |  4.5   |  2.5   |  3.5   |
ManagerRating |  4.5   |  2.5   |  3.5   |
NotSelfRating |  4.5   |  2.5   |  3.5   |

Other employees would have less columns since they don't have data for previous years.

其他员工的列数较少,因为他们没有前几年的数据。

I'm having issues with filtering it down to a specific employee and sorting the years by their review date (the name isn't always a reliable way to sort them).

我遇到了将其过滤到特定员工并按其审核日期对年份进行排序的问题(名称并不总是一种可靠的排序方式)。

In the end I'm looking to use this as the data for a report.

最后,我希望将此作为报告的数据。

If there is a different way than a crosstab query to accomplish this I would be okay with that as well.

如果有一个不同于交叉表查询的方法来实现这一点,我也可以。

Thanks!

1 个解决方案

#1

You need a column for all the rating types, not an individual column for each type. If you can't redesign the table, I would suggest creating a new one for your purposes. The below uses a union to add in that type column referred to above. You create a column and hardcode the value (SelfRating, ManagerRating, etc):

您需要所有评级类型的列,而不是每种类型的单个列。如果您无法重新设计表格,我建议您为自己的目的创建一个新表格。下面使用union来添加上面提到的那个类型列。您创建一个列并对该值进行硬编码(SelfRating,ManagerRating等):

SELECT * INTO EmployeeRatings
FROM (SELECT tblEmployeeYear.EmployeeId AS EmpId, ReviewYearId, "SelfRating" AS Category, SelfRating AS Score
      FROM tblEmployeeYear
      WHERE SelfRating Is Not Null
      UNION ALL
      SELECT tblEmployeeYear.EmployeeId, ReviewYearId, "ManagerRating", ManagerRating
      FROM tblEmployeeYear
      WHERE ManagerRating Is Not Null
      UNION ALL
      SELECT tblEmployeeYear.EmployeeId, ReviewYearId, "NotSelfRating", NotSelfRating
      FROM tblEmployeeYear
      WHERE NotSelfRating Is Not Null)

Then use the newly created table in place of tblEmployeeYear. Note that I use Year([ReviewDate]) which will return only the year. Also, since it looks like it may be possible to have more than one of each review type per year, I averaged the Score for the year.

然后使用新创建的表代替tblEmployeeYear。请注意,我使用Year([ReviewDate]),它只返回年份。此外,由于看起来每年可能有多个评论类型,我的平均分数为年度。

TRANSFORM Avg(Score)
SELECT EmpId, Category
FROM (SELECT EmpId, Category, ReviewDate, Score
      FROM tblReviewYear 
      INNER JOIN EmployeeRatings
              ON tblReviewYear.ID = EmployeeRatings.ReviewYearID) AS Reviews
GROUP BY EmpId, Category
PIVOT Year([ReviewDate]);