动态列 - SQL服务器 - 月为列

Question

DB：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数据透视表功能的任何想法？

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不执行的执行计划中使用Filter。

步骤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) 

Answer 2

我想你可以用一个嵌套的while循环和一些动态SQL来做到这一点。如果您不能保存最终表格，或者每个月都必须重新生成所有列，这将是一个缓慢的解决方案。但是，如果它只是添加剂，那么它可能并不坏。无论如何，这是我该怎么做：

1. Out循环选择最古老的一年。
2. 内循环选择第一个月。
3. Inside Inner loop - 在表格中添加一个名称为 - 的列。
4. 里面内环 - 更新表的所有信息新 - 动态SQL
5. 迭代通过每年的外环每个月
6. 迭代柱通过内循环。

Answer 3

我写了一个名为pivot_query一个存储过程，可以帮助与此，来源是here ，例如原始数据here。

With your data: 

create table ProjectData 
    (
    Project      varchar(20), 
    [Year]      Integer, 
    Jan       decimal(12,2), 
    Feb       decimal(12,2), 
    Mar       decimal(12,2), 
    Apr       decimal(12,2), 
    May       decimal(12,2), 
    Jun       decimal(12,2), 
    Jul       decimal(12,2), 
    Aug       decimal(12,2), 
    Sep       decimal(12,2), 
    Oct       decimal(12,2), 
    Nov       decimal(12,2), 
    Dec       decimal(12,2) 
    ); 

insert into ProjectData values ('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); 
insert into ProjectData values ('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); 
insert into ProjectData values ('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) ; 
insert into ProjectData values ('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); 

declare @mySQL varchar(MAX) 

set @mySQL = 'select * from ProjectData' 

exec pivot_query @mySQL, 'Project', 'Year', 'max(Jan) Jan,max(Feb) Feb,max(Mar) Mar,max(Apr) Apr,max(Jun) Jun,max(Jul) Jul,max(Aug) Aug,max(Sep) Sep,max(Oct) Oct,max(Nov) Nov,max(Dec) Dec' 

Results: 
Project    2008_Jan  2008_Feb  2008_Mar  2008_Apr  2008_Jun  2008_Jul  2008_Aug  2008_Sep  2008_Oct  2008_Nov  2008_Dec  2009_Jan  2009_Feb  2009_Mar  2009_Apr  2009_Jun  2009_Jul  2009_Aug  2009_Sep  2009_Oct  2009_Nov  2009_Dec  2010_Jan  2010_Feb  2010_Mar  2010_Apr  2010_Jun  2010_Jul  2010_Aug  2010_Sep  2010_Oct  2010_Nov  2010_Dec 
-------------------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ 
01-11052    NULL   NULL   NULL   NULL   NULL   NULL   NULL   NULL   NULL   NULL   NULL   56131.00  .00   36962.00  -61596.00 84.00  .00   .00   .00   .00   .00   .00   NULL   NULL   NULL   NULL   NULL   NULL   NULL   NULL   NULL   NULL   NULL 
11-11079    .00   .00   .00   .00   .00   .00   .00   .00   .00   .00   75244.90  466.00  .00   .00   .00   .00   .00   .00   .00   .00   .00   .00   855.00  .00   .00   .00   .00   .00   .00   .00   .00   .00   .00 

不确切但相当的接近。 :-)