SQL Server:带有标题的动态数据透视表以包括列名和日期 [英] SQL Server: Dynamic pivot with headers to include column name and date

问题描述

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

I am trying to use dynamic pivot and need help on converting rows to columns

表格如下:

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

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

and I need for reporting purposes like

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

推荐答案

为了同时获得 expense 和 revenue 列作为带有 date，我建议同时应用 UNPIVOT 和 PIVOT 函数.

In order to get both the expense and revenue columns as headers with the date, I would recommend applying both the UNPIVOT and the PIVOT functions.

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

The UNPIVOT will convert the expense and revenue columns into rows that you can append the date to. Once the date is added to the column names, then you can apply the PIVOT function.

UNPIVOT 代码将是:

The UNPIVOT code will be:

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

请参阅 SQL Fiddle with Demo.这会产生一个结果:

See SQL Fiddle with Demo. This produces a result:

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

如您所见，费用/收入列现在是带有 new_col 的行，该行是通过将日期连接到末尾来创建的.然后在 PIVOT 中使用此 new_col:

As you can see the expense/revenue columns are now rows with a new_col that has been created by concatenating the date to the end. This new_col is then used in the 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 with Demo.

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

The above version will work great if you have a known number of dates to turn into columns but if you have an unknown number of dates, then you will want to use dynamic SQL to generate the result:

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

参见 SQL Fiddle with Demo.两个查询生成相同的结果.

See SQL Fiddle with Demo. Both queries generate the same result.

这篇关于SQL Server:带有标题的动态数据透视表以包括列名和日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！