sql - 动态列 - SQL Server - 月份作为列

Question

数据库:SQL Server 2005

我们有一个以这种方式包含数据的表:

Project              Year        Jan                   Feb                   Mar                   Apr                   May                   Jun                   Jul                   Aug                   Sep                   Oct                   Nov                   Dec
-------------------- ----------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- ---------------------
11-11079             2008        0.00                  0.00                  0.00                  0.00                  0.00                  0.00                  0.00                  0.00                  0.00                  0.00                  0.00                  75244.90
11-11079             2009        466.00                0.00                  0.00                  0.00                  0.00                  0.00                  0.00                  0.00                  0.00                  0.00                  0.00                  0.00
11-11079             2010        855.00                0.00                  0.00                  0.00                  0.00                  0.00                  0.00                  0.00                  0.00                  0.00                  0.00                  0.00  
01-11052             2009        56131.00              0.00                  36962.00              -61596.00             2428.00               84.00                 0.00                  0.00                  0.00                  0.00                  0.00                  0.00

有人希望数据显示为整个项目的一行。这些列将是动态的，取决于它进入 future 的年数。一个例子是:

Project        Jan-2009     Feb-2009     Mar-2009     Apr-2009... Dec-2009     Jan-2010
-------------- ------------ ------------ ------------ ----------- ------------ ---------
11-11079       466.00       0.00         0.00         0.00        0.00         855.00    
01-11052       56131.00     0.00         36962.00     -61596.00   2428.00      0.00

我读过许多示例，其中每个条目的日期填充在一列中，但我没有发现月份是列名而年份在行中的任何情况。

带有数据透视表的动态 SQL？
或者使用 SQL、临时表、连接和联合进行一些非常广泛的操作？
关于使用 SSIS 数据透视表功能的任何想法？

Answer 1

您的数据已经进行了透视，但需要在不同的级别进行透视。我认为处理此问题的最佳方法是先将其取消旋转，然后再处理正确的旋转水平。

第 1 步:取消旋转

您可以使用 SQL 2005 UNPIVOT命令，或使用 CROSS JOIN 技术。以下是两者的示例。注意我在中间省略了几个月以保持简单。只需将它们添加进去。

-- CROSS JOIN method (also works in SQL 2000)
SELECT
   P.Project,
   Mo =
      DateAdd(mm,
         X.MonthNum,
         DateAdd(yy, P.[Year] - 1900, '19000101')
      ),
   Amount = 
      CASE X.MonthNum
         WHEN 0 THEN Jan
         WHEN 1 THEN Feb
         WHEN 11 THEN Dec
      END
FROM
   ProjectData P
   CROSS JOIN (
      SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 11
   ) X (MonthNum)

每行重复 12 次，然后 CASE 语句只为每一行提取一个月，从而使数据很好地未旋转。

-- UNPIVOT method
SELECT
    P.Project,
    Mo =
       DateAdd(mm,
          Convert(int, P.MonthNum),
          DateAdd(yy, P.[Year] - 1900, '19000101')
       ),
    P.Amount
FROM
   (
      SELECT Project, [Year], [0] = Jan, [1] = Feb, [11] = Dec
      FROM ProjectData
   ) X UNPIVOT (Amount FOR MonthNum IN ([0], [1], [11])) P

DROP TABLE ProjectData

这两种方法都不是一直以来明显的性能赢家。有时一个比另一个更有效(取决于被透视的数据)。 UNPIVOT 方法在执行计划中使用了 CROSS JOIN 没有的过滤器。

第 2 步:再次旋转

现在，如何使用非透视数据。您没有说明您的某人将如何使用它，但是由于您需要将数据放入某种类型的输出文件中，我建议使用 SSRS(Sql Server Reporting Services)，它与 SQL Server 2005 一起提供额外费用。

只需使用 Matrix报告对象以透视上述查询之一。这个对象愉快地确定了在报表运行时制作成列标签的数据值，听起来正是您所需要的。如果您添加一列完全按照您喜欢的方式格式化日期，那么您可以按 Mo 列排序，但使用新表达式作为列标签。

SSRS 还具有多种可用的格式和调度选项。例如，您可以让它通过电子邮件发送 Excel 文件或将网页保存到文件共享。

如果我遗漏了什么，请告诉我。

对于任何想要查看上面代码的人，这里有一些创建脚本供您使用:

USE tempdb

CREATE TABLE ProjectData (
    Project varchar(10),
    [Year] int,
    Jan decimal(15, 2),
    Feb decimal(15, 2),
    Dec decimal(15, 2)
)

SET NOCOUNT ON

INSERT ProjectData VALUES ('11-11079', 2008, 0.0, 0.0, 75244.90)
INSERT ProjectData VALUES ('11-11079', 2009, 466.0, 0.0, 0.0)
INSERT ProjectData VALUES ('11-11079', 2010, 855.0, 0.0, 0.0)
INSERT ProjectData VALUES ('01-11052', 2009, 56131.0, 0.0, 0.0)