tSQL如何编写返回一个动态大小表的视图/函数。

I have recently written a script in t-SQL which uses dynamic SQL to generate a table. The output of the script varies, depending on when it is run. The output is something like this:

我最近在t-SQL中编写了一个脚本，它使用动态SQL生成一个表。脚本的输出是不同的，取决于它运行的时间。输出是这样的:

Group   2010    2011   2012   2013
  A      1       2       3      2
  B      4       3       3      4
  C      4       3       1      1

However, each year another year is added onto the table, meaning the table size varies.

然而，每一年又有一年被添加到表中，这意味着表的大小各不相同。

e.g.

如。

Group   2010    2011   2012   2013   2014
  A      1       2       3      2     2
  B      4       3       3      4     2
  C      4       3       1      1     3

I need to be able to access the data in this table via access to generate some reports, so require some sort of view or function to get the data.

我需要能够通过访问生成一些报告来访问这个表中的数据，因此需要某种视图或函数来获取数据。

What is the best way of doing this?

最好的方法是什么?

1 个解决方案

#1

if you have to use this output in report. Than you have to fix column name in SQL as below.

如果你必须在报告中使用这个输出。您必须在SQL中固定列名，如下所示。

Group  year4    year3    year2   year1
  A      1       2        3       2
  B      4       3        3       4
  C      4       3        1       1

and in report tools you can convert year1 = current year, year2 = current year - 1 and so on.

在报告工具中，你可以转换year1 = current year, year2 = current year - 1，等等。

update 2

更新2

using this method you can easily design your report.

使用此方法，您可以轻松地设计报表。

Group   year5  year4   year3   year2   year1
  A      1       2       3       2      2
  B      4       3       3       4      2
  C      4       3       1       1      3

#1