在SQL Server 2005中将列显示为行

小编典典

在SQL Server 2005中将列显示为行

sql

我已经阅读了数十种将要解决的类似移调问题的解决方案，但奇怪的是，没有任何一种解决方案可以完全反映我的问题。我只是试图将我的行翻转为简单的仪表板类型数据集中的列。

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

DatePeriod  PeriodNumberOverall   Transactions   Customers   Visits

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

并且我希望能够生成以下内容：

                      Jan 2012   Feb 2012   Mar 2012

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

指标将是静态的（交易，客户和访问），但日期段将是动态的（即，IE-随着月份的增加而增加）。

再次，我准备了许多利用数据透视，取消透视，存储过程，UNION
ALL等的示例，但是在我不进行任何汇总的情况下，什么也没做，只是从字面上移置了整个输出。我还发现在Visual Studio2005中使用带有嵌入式列表的矩阵可以轻松实现此目的，但是我无法将最终输出导出到excel，这是必需的。任何帮助将不胜感激。

阅读 180

2021-03-23

共1个答案

小编典典

为了获得所需的结果，您需要首先提供UNPIVOT数据，然后是PIVOT theDatePeriod`值。

该UNPIVOT将变换的多列Transactions，Customers并Visits为多行。其他答案使用aUNION ALL取消，但是SQL Server 2005UNPIVOT是支持该功能的第一年。

要取消数据透视表的查询是：

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

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

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

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

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。

如果您有未知的日期期限，那么您将使用动态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。两者都会给出结果：

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

2021-03-23