时间戳

首页 > 解决方案 > 在 SQL Server 中将行转为列，将列转为行

sql-server - 在 SQL Server 中将行转为列，将列转为行

问题描述

我正在使用 SQL Sever 2012。我有下表：

CREATE TABLE #temptable
(
TimePeriod VARCHAR (50)
, Transactions INT
, SalesAmount DECIMAL (18, 2)
, NoEmployees INT
, Items INT
, Returns INT
)

INSERT INTO #temptable
VALUES
    ('ThisMonth', 5000, 2343423.41, 230, 1000, 200)
  , ('LastMonth', 6000, 433245.50, 232, 1020, 109)
  , ('ThisYear', 50000, 1391468.43, 321, 14890, 564)
  , ('LastYear', 60000, 1699713.64, 311, 16123, 342)

SELECT  *
FROM    #temptable

我想将行旋转到列，将列标题旋转到行。我想要结束的表如下所示：

SELECT 'Transactions' Metric, 5000 ThisMonth,   6000    LastMonth, 50000 ThisYear,  60000 LastYear UNION
SELECT 'SalesAmount' Metric,    2343423.41, 433245.5,   1391468.43  ,1699713.64 UNION
SELECT 'NoEmployees' Metric,    230,    232 ,321    ,311 UNION 
SELECT 'Items'  Metric, 1000,   1020,   14890,  16123 UNION
SELECT 'Returns' Metric,    200 ,109    ,564    ,342

作为起点，我尝试过：

SELECT  p.ThisMonth
      , p.LastMonth
      , p.ThisYear
      , p.LastYear
FROM    #temptable
    PIVOT(   MAX(Transactions)
             FOR TimePeriod IN(ThisMonth, LastMonth, ThisYear, LastYear)) p

标签： sql-serverpivot

解决方案

如果您取消旋转，然后再次旋转，可以实现结果：

with cte as (
        /* this is the equivalent of an "unpivot" but using cross apply instead */
        select
            ca.*
        from #temptable
        cross apply (
            values
               (TimePeriod, 'Transactions', Transactions)
             , (TimePeriod, 'SalesAmount', SalesAmount)
             , (TimePeriod, 'NoEmployees', NoEmployees)
             , (TimePeriod, 'Items', Items)
             , (TimePeriod, 'Returns', Returns)
          ) ca (hdg, metric, val)
        )
SELECT metric,  [ThisMonth],[LastMonth], [ThisYear], [LastYear]
FROM cte
pivot (
        max([val])
        FOR [hdg] IN ([ThisMonth],[LastMonth], [ThisYear], [LastYear])
      ) p      
;

结果：

          metric      ThisMonth    LastMonth    ThisYear     LastYear   
 ---- -------------- ------------ ----------- ------------ ------------ 
   1   Items             1000,00     1020,00     14890,00     16123,00  
   2   NoEmployees        230,00      232,00       321,00       311,00  
   3   Returns            200,00      109,00       564,00       342,00  
   4   SalesAmount    2343423,41   433245,50   1391468,43   1699713,64  
   5   Transactions      5000,00     6000,00     50000,00     60000,00

见：https ://rextester.com/EIXT79068

或者，通过使用蛮力：

SELECT 
      'Items' as Metric
    , max(case when TimePeriod = 'ThisMonth' then Items end) ThisMonth
    , max(case when TimePeriod = 'LastMonth' then Items end) LastMonth
    , max(case when TimePeriod = 'ThisYear'  then Items end) ThisYear
    , max(case when TimePeriod = 'LastYear'  then Items end) LastYear
FROM    #temptable
union all
SELECT 
      'NoEmployees' as Metric
    , max(case when TimePeriod = 'ThisMonth' then NoEmployees end) ThisMonth
    , max(case when TimePeriod = 'LastMonth' then NoEmployees end) LastMonth
    , max(case when TimePeriod = 'ThisYear'  then NoEmployees end) ThisYear
    , max(case when TimePeriod = 'LastYear'  then NoEmployees end) LastYear
FROM    #temptable
union all
SELECT 
      'Returns' as Metric
    , max(case when TimePeriod = 'ThisMonth' then Returns end) ThisMonth
    , max(case when TimePeriod = 'LastMonth' then Returns end) LastMonth
    , max(case when TimePeriod = 'ThisYear'  then Returns end) ThisYear
    , max(case when TimePeriod = 'LastYear'  then Returns end) LastYear
FROM    #temptable
union all
SELECT 
      'SalesAmount' as Metric
    , max(case when TimePeriod = 'ThisMonth' then SalesAmount end) ThisMonth
    , max(case when TimePeriod = 'LastMonth' then SalesAmount end) LastMonth
    , max(case when TimePeriod = 'ThisYear'  then SalesAmount end) ThisYear
    , max(case when TimePeriod = 'LastYear'  then SalesAmount end) LastYear
FROM    #temptable
union all
SELECT 
      'Transactions' as Metric
    , max(case when TimePeriod = 'ThisMonth' then transactions end) ThisMonth
    , max(case when TimePeriod = 'LastMonth' then transactions end) LastMonth
    , max(case when TimePeriod = 'ThisYear'  then transactions end) ThisYear
    , max(case when TimePeriod = 'LastYear'  then transactions end) LastYear
FROM    #temptable
;

我的猜测是，在更改您用来访问 #temptable 的查询时，这会变得容易得多

推荐阅读