动态列-SQL Server-月作为列

admin

动态列-SQL Server-月作为列

sql

资料库：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

有人希望数据在整个项目中显示为一行。这些列将是动态的，具体取决于将来要运行多少年。一个例子是：

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数据透视表功能有什么想法？

阅读 222

2021-06-07

共1个答案

admin

您的数据已经被旋转，但是需要在另一个层次上旋转。我认为处理此问题的最佳方法是先取消枢转，然后再处理正确的枢轴水平。

第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：再次旋转

现在，如何使用未透视的数据。您没有说您的人将如何使用它，但是由于您需要将数据放入某种输出文件中，因此我建议使用SQL Server
2005附带的SSRS（Sql Server Reporting Services）。额外费用。

只需使用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)

2021-06-07