logo标签列表

在SQL Server 2005中将列显示为行

sqlt-sqlsql-server-2005pivot
6

我已经阅读了数十个类似的转置问题的解决方案，但很奇怪，没有一个完全符合我的问题。我只是想在一个简单的仪表板类型数据集中将行翻转为列。

从各种交易表中提取的数据如下：

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

指标是静态的（交易、客户和访问量），但日期周期是动态的（例如，随着月份的增加而增加）。

我已经看了很多利用透视表、反转、存储过程、UNION ALL等的示例，但没有任何一个不进行聚合操作，只是简单地转置整个输出的方法。我也在Visual Studio 2005中找到了一种简单的方法，使用带有嵌入列表的矩阵，但我无法将最终输出导出到Excel，这是一个要求。任何帮助都将不胜感激。

请查看Bluefeet在https://dev59.com/imYr5IYBdhLWcg3w0tWE中的答案，以检查动态枢轴版本。 - Nenad Zivkovic
3个回答
2
您需要动态地创建一个带有PIVOT和APPLY运算符的SQL语句，并立即运行该命令。如果您的指标是静态的（交易、客户和访问），因此我们可以使用CROSS APPLY运算符与VALUES作为表源。对于SQL Server2008+。
DECLARE @cols nvarchar( max),
        @query nvarchar(max)
SELECT @cols =
  STUFF((SELECT ',' + QUOTENAME(t.DatePeriod) AS ColName                               
         FROM dbo.test62 t                   
         FOR XML PATH(''), TYPE).value ('.', 'nvarchar(max)'), 1, 1, '')     

SET @query =
 'SELECT *
  FROM (
        SELECT t.DatePeriod, COALESCE(o.Transactions, o.Customers, o.Visits) AS PvtVals, o.PvtColumns, o.OrderColumns 
        FROM dbo.test62 t CROSS APPLY (
                                       VALUES(t.Transactions, NULL, NULL, ''Transaction'', 1),
                                             (NULL, t.Customers, NULL, ''Customers'', 2),
                                             (NULL, NULL, t.Visits, ''Visits'', 3)
                                       ) o (Transactions, Customers, Visits, PvtColumns, OrderColumns)
        ) p
  PIVOT
   (      
    MAX(PvtVals) FOR DatePeriod IN (' + @cols + ')
    ) AS pvt
  ORDER BY pvt.OrderColumns '
EXEC(@query)

结果：

PvtColumns  Jan 2012 Fed 2012 Mar 2012
Transaction 100      200      300
Customers   50       100      200
Visits      150      300      600

在SQLFiddle上的演示。 适用于SQL Server 2005。
DECLARE @cols nvarchar( max),
        @query nvarchar(max)
SELECT @cols =
  STUFF((SELECT ',' + QUOTENAME(t.DatePeriod) AS ColName                               
         FROM dbo.test62 t                   
         FOR XML PATH(''), TYPE).value ('.', 'nvarchar(max)'), 1, 1, '')     

SET @query =
 'SELECT *
  FROM (
        SELECT t.DatePeriod, COALESCE(o.Transactions, o.Customers, o.Visits) AS PvtVals, o.PvtColumns, o.OrderColumns
        FROM dbo.test62 t CROSS APPLY (
                                       SELECT t.Transactions, NULL, NULL, ''Transaction'', 1
                                       UNION ALL 
                                       SELECT NULL, t.Customers, NULL, ''Customers'', 2
                                       UNION ALL 
                                       SELECT NULL, NULL, t.Visits, ''Visits'', 3
                                       ) o (Transactions, Customers, Visits, PvtColumns, OrderColumns)
        ) p
  PIVOT
   (      
    MAX(PvtVals) FOR DatePeriod IN (' + @cols + ')
    ) AS pvt
  ORDER BY pvt.OrderColumns'
EXEC(@query)
1
“CROSS APPLY ... VALUES” 在2005年不起作用。但是您可以使用“CROSS APPLY（SELECT t.Transactions，NULL，NULL，“Transaction” UNION ALL SELECT NULL，t.Customers，NULL，“Customers” UNION ALL ...）”。 - Martin Smith
谢谢马丁的解释！;)回答已更新。顺便问一下，如果使用CROSS APPLY (SELECT Transactions，Customers，Visits，PvtColumns FROM (VALUES (t.Transactions，NULL，NULL，''Transaction'')，(NULL，t.Customers，NULL，''Customers'')，(NULL，NULL，t.Visits，''Visits''))x(Transactions，Customers，Visits，PvtColumns)) o...这样会起作用吗？ - Aleksandr Fedorenko
非常感谢您提供的精彩答案！我唯一剩下的问题是是否有简单的方法可以按照我想要的顺序对指标进行排序？在所有三个答案中，指标都按字母顺序重新排序了。再次感谢！ - user2234794
2
为了得到您想要的结果，需要首先对数据进行UNPIVOT操作，然后再对DatePeriod值进行PIVOT操作。 UNPIVOT将把Transactions、Customers和Visits这些列转换为多行。其他答案使用UNION ALL进行逆转，但SQL Server 2005是支持UNPIVOT函数的第一年。
执行反转数据的查询语句如下：
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 |
非常感谢您提供的精彩答案！我唯一剩下的问题是是否有简单的方法可以按照我想要的顺序对指标进行排序？在所有三个答案中，指标都按字母顺序重新排序了。再次感谢！ - user2234794
@user2234794，请看我的编辑，我更新了我的答案，包括按列的初始位置排序。 - Taryn
感谢您的更新。我将使用的主要“表”不会成为模式的一部分 - 它将是从多个其他表中提取的数据元素（聚合指标/字段）放入临时表中。如果我没有从模式中存在的单个表中提取字段，是否有另一种排序方式？如果问题太简单/基础，请原谅 - 我绝不是T-SQL专家。 - user2234794
@user2234794 是的，你可以加入一个包含排序顺序的派生表。请看我的编辑。 - Taryn
1
如果您能提前知道有多少个不同的日期周期，那么您可以使用以下固定查询：

;with CTE_UNIONTable
as 
(
select [DatePeriod],[PeriodNumberOverall],[Transactions] as [value], 'Transactions' as subType from table1
UNION ALL 
select [DatePeriod],[PeriodNumberOverall],[Customers] as [value], 'Customers' as subType from table1
UNION ALL 
select [DatePeriod],[PeriodNumberOverall],[Visits] as [value], 'Visits' as subType from table1
), CTE_MiddleResult
as 
(
select * from CTE_UNIONTable
    pivot 
    (
       max(value) 
       for DatePeriod in ([Jan 2012],[Feb 2012],[Mar 2012])
     ) as P
     )
select SubType, max([Jan 2012]) as [Jan 2012] ,max([Feb 2012]) as [Feb 2012], max([Mar 2012]) as [Feb 2012]
from CTE_MiddleResult
group by SubType

SQL FIDDLE DEMO

如果日期期间不确定，则 @Alexander 已经给出了解决方案，下面的代码只是一个第二意见，不使用APPLY，而是使用UNION ALL


DECLARE @cols nvarchar( max),
        @query nvarchar (max),
        @selective nvarchar(max)
SELECT @cols =
  STUFF((SELECT ',' + QUOTENAME(t.DatePeriod) AS ColName                               
         FROM table1 t                   
         FOR XML PATH( ''), TYPE).value ('.', 'nvarchar(max)'),1,1,'')

SELECT @selective =
  STUFF((SELECT ',MAX(' + QUOTENAME(t.DatePeriod) +') as ' + QUOTENAME(t.DatePeriod)  AS ColName                               
         FROM table1 t                   
         FOR XML PATH( ''), TYPE).value ('.', 'nvarchar(max)'),1,1,'')

set @query = '
;with CTE_UNIONTable
as 
(
select [DatePeriod],[PeriodNumberOverall],[Transactions] as [value], ''Transactions'' as subType from table1
UNION ALL 
select [DatePeriod],[PeriodNumberOverall],[Customers] as [value], ''Customers'' as subType from table1
UNION ALL 
select [DatePeriod],[PeriodNumberOverall],[Visits] as [value], ''Visits'' as subType from table1
), CTE_MiddleResult
as 
(
select * from CTE_UNIONTable
    pivot 
    (
       max(value) 
       for DatePeriod in ('+@cols+')
     ) as P
)
select SubType,' + @selective + ' 
from CTE_MiddleResult
group by SubType'

exec(@query)

SQL FIDDLE DEMO

在 HTML 中，保留原文不变。该文本包含一个段落和加粗的文本，其中链接名称为 "SQL FIDDLE DEMO"。
非常感谢您提供的精彩答案！我唯一剩下的问题是是否有简单的方法可以按照我想要的顺序对指标进行排序？在所有三个答案中，指标都按字母顺序重新排序了。再次感谢！ - user2234794
你可以在最后添加ORDER BY，但是无论是升序还是降序，你想要的确切顺序，也许代码需要根据你想要的顺序进行一些更新。 - ljh
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接