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提琴.这样会产生结果:

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提琴.

如果您知道要转换成列的日期数，但是如果日期数未知，那么上面的版本将非常有用，那么您将需要使用动态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提琴.这两个查询都会产生相同的结果.

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

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