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

小编典典

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

sql

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

该表如下所示：

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

阅读 189

2021-04-15

共1个答案

小编典典

为了将expense和和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

请参阅带有演示的SQL Fiddle。这将产生结果：

| 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;

请参阅带有演示的SQL Fiddle。

如果您知道要转换成列的日期数量，但是如果日期数量未知，那么上面的版本将非常有用，但是如果要使用动态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);

2021-04-15