SQL Server：带有标题的动态数据透视表，包含列名称和日期

我正在尝试使用动态数据透视表，并且需要有关将行转换为列的帮助

该表看起来像：

ID   expense  revenue date
1    43       45      12-31-2012   
1    32       32      01-01-2013 
3    64       56      01-31-2013
4    31       32      02-31-2013

我需要出于报告目的，例如

ID  expense12-31-2012  expense01-01-2013 expense01-31-2013 revenue12-31-2013
1   43                 32                
3                                        64

为了同时获得expense and revenue列作为标题date，我建议同时应用 UNPIVOT 和 PIVOT 函数。

UNPIVOT 会将费用和收入列转换为您可以附加日期的行。将日期添加到列名称后，您就可以应用 PIVOT 函数。

UNPIVOT 代码将是：

select id, 
  col+'_'+convert(varchar(10), date, 110) new_col, 
  value
from yt
unpivot
(
  value
  for col in (expense, revenue)
) un

See SQL 摆弄演示 http://www.sqlfiddle.com/#!3/30421/7。这会产生一个结果：

| ID |            NEW_COL | VALUE |
-----------------------------------
|  1 | expense_12-31-2012 |    43 |
|  1 | revenue_12-31-2012 |    45 |
|  2 | expense_01-01-2013 |    32 |

正如您所看到的，费用/收入列现在是带有new_col它是通过将日期连接到末尾而创建的。这new_col然后在 PIVOT 中使用：

select id,
  [expense_12-31-2012], [revenue_12-31-2012],
  [expense_01-01-2013], [revenue_01-01-2013],
  [expense_01-31-2013], [revenue_01-31-2013],
  [expense_03-03-2013], [revenue_03-03-2013]
from
(
  select id, 
    col+'_'+convert(varchar(10), date, 110) new_col, 
    value
  from yt
  unpivot
  (
    value
    for col in (expense, revenue)
  ) un
) src
pivot
(
  sum(value)
  for new_col in ([expense_12-31-2012], [revenue_12-31-2012],
                  [expense_01-01-2013], [revenue_01-01-2013],
                  [expense_01-31-2013], [revenue_01-31-2013],
                  [expense_03-03-2013], [revenue_03-03-2013])
) piv;

See SQL 摆弄演示 http://www.sqlfiddle.com/#!3/30421/6.

如果您有已知数量的日期要转换为列，那么上面的版本将非常有效，但如果您有未知数量的日期，那么您将需要使用动态 SQL 来生成结果：

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(c.col+'_'+convert(varchar(10), yt.date, 110)) 
                    from yt
                    cross apply
                    (
                      select 'expense' col union all
                      select 'revenue'
                    ) c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT id,' + @cols + ' 
            from
            (
              select id, 
                col+''_''+convert(varchar(10), date, 110) new_col, 
                value
              from yt
              unpivot
              (
                value
                for col in (expense, revenue)
              ) un
            ) src
            pivot 
            (
                sum(value)
                for new_col in (' + @cols + ')
            ) p '

execute(@query);

See SQL 摆弄演示 http://www.sqlfiddle.com/#!3/30421/8。两个查询生成相同的结果。