在 SQL Server 2005 中将列显示为行 [英] Displaying Columns as Rows in SQL Server 2005

问题描述

我已经阅读了数十种与我即将提出的类似换位问题的解决方案，但奇怪的是没有一个完全反映我的问题.我只是想在一个简单的仪表板类型数据集中将我的行翻转为列.

I have read dozens of solutions to similar transposition problems as the one I am about to propose but oddly none that exactly mirrors my issue. I am simply trying to flip my rows to columns in a simple dashboard type data set.

从各种事务表中提取的数据如下所示:

The data when pulled from various transaction tables looks like this:

DatePeriod  PeriodNumberOverall   Transactions   Customers   Visits

'Jan 2012'   1                    100            50          150
'Feb 2012'   2                    200            100         300
'Mar 2012'   3                    300            200         600

我希望能够生成以下内容:

and I want to be able to generate the following:

                      Jan 2012   Feb 2012   Mar 2012

Transactions          100        200        300
Customers             50         100        200
Visits                150        300        600

指标将是静态的(交易、客户和访问)，但日期期间将是动态的(IE - 随着月份的推移会增加更多).

The metrics will be static (Transactions, Customers and Visits), but the date periods will be dynamic (IE - more added as months go by).

再次，我准备了许多利用 pivot、unpivot、存储过程、UNION ALL 等的示例，但我没有做任何聚合，只是从字面上转置整个输出.我还在 Visual Studio 2005 中找到了一种使用带有嵌入式列表的矩阵的简单方法，但我无法将最终输出导出到 excel，这是必需的.任何帮助将不胜感激.

Again, I have ready many examples leveraging pivot, unpivot, store procedures, UNION ALLs, etc, but nothing where I am not doing any aggregating, just literally transposing the whole output. I have also found an easy way to do this in Visual Studio 2005 using a matrix with an embedded list, but I can't export the final output to excel which is a requirement. Any help would be greatly appreciated.

推荐答案

为了得到你想要的结果你需要先UNPIVOT数据然后PIVOTDatePeriod` 值.

In order to get the result that you want you need to first UNPIVOT the data and then PIVOT theDatePeriod` Values.

UNPIVOT 会将 Transactions、Customers 和 Visits 的多列转换为多行.其他答案是使用 UNION ALL 来取消透视，但 SQL Server 2005 是支持 UNPIVOT 函数的第一年.

The UNPIVOT will transform the multiple columns of Transactions, Customers and Visits into multiple rows. The other answers are using a UNION ALL to unpivot but SQL Server 2005 was the first year the UNPIVOT function was supported.

取消透视数据的查询是:

The query to unpivot the data is:

select dateperiod,
  col, value
from transactions
unpivot
(
  value for col in (Transactions, Customers, Visits)
) u

参见演示.这会将您当前的列转换为多行，因此数据如下所示:

See Demo. This transforms your current columns into multiple rows, so the data looks like the following:

| DATEPERIOD |          COL | VALUE |
-------------------------------------
|   Jan 2012 | Transactions |   100 |
|   Jan 2012 |    Customers |    50 |
|   Jan 2012 |       Visits |   150 |
|   Feb 2012 | Transactions |   200 |

现在，由于数据在行中，您可以将 PIVOT 函数应用于 DatePeriod 列:

Now, since the data is in rows, you can apply the PIVOT function to the DatePeriod column:

select col, [Jan 2012], [Feb 2012], [Mar 2012]
from
(
  select dateperiod,
    t.col, value, c.SortOrder
  from
  (
    select dateperiod,
      col, value
    from transactions
    unpivot
    (
      value for col in (Transactions, Customers, Visits)
    ) u
  ) t
  inner join
  (
    select 'Transactions' col, 1 SortOrder
    union all
    select 'Customers' col, 2 SortOrder
    union all
    select 'Visits' col, 3 SortOrder
   ) c
    on t.col = c.col
) d
pivot
(
  sum(value)
  for dateperiod in ([Jan 2012], [Feb 2012], [Mar 2012])
) piv
order by SortOrder;

请参阅 SQL Fiddle with Demo.

如果您有未知数量的日期周期，那么您将使用动态 SQL:

If you have an unknown number of date period's then you will use dynamic SQL:

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

select @cols = STUFF((SELECT ',' + QUOTENAME(dateperiod) 
                    from transactions
                    group by dateperiod, PeriodNumberOverall
                    order by PeriodNumberOverall
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT col, ' + @cols + ' 
             from 
             (
                select dateperiod,
                  t.col, value, c.SortOrder
                from
                (
                  select dateperiod,
                    col, value
                  from transactions
                  unpivot
                  (
                    value for col in (Transactions, Customers, Visits)
                  ) u
                ) t
                inner join
                (
                  select ''Transactions'' col, 1 SortOrder
                  union all
                  select ''Customers'' col, 2 SortOrder
                  union all
                  select ''Visits'' col, 3 SortOrder
                 ) c
                  on t.col = c.col
            ) x
            pivot 
            (
                sum(value)
                for dateperiod in (' + @cols + ')
            ) p 
            order by SortOrder'

execute(@query)

参见 SQL Fiddle with Demo.两者都会给出结果:

See SQL Fiddle with Demo. Both will give the result:

|          COL | JAN 2012 | FEB 2012 | MAR 2012 |
-------------------------------------------------
| Transactions |      100 |      200 |      300 |
|    Customers |       50 |      100 |      200 |
|       Visits |      150 |      300 |      600 |

这篇关于在 SQL Server 2005 中将列显示为行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！