将总行添加到枢轴底部

Big Pimpin 发表于 Dev

大皮平

我看到使用GROUP BY CUBE()会在透视表的底部添加一个总计行，并且我一直在尝试使我的语法准确，以便我可以这样做。好吧，我正在运行我的语法，但是在结果集的底部没有得到“总计”。这是为什么？

select *
FROM
(
  select case 
  WHEN a.state LIKE 'CA' THEN 'California'
  WHEN a.state LIKE 'WA' THEN 'Washington'
  else a.state
  end As [Full State], 
  SaleID As [Sales By State], 
  CONVERT(VARCHAR(20), dt.CumulativeWeek) AS Week
  FROM retailsales.Store1 a
  INNER JOIN retailsales.customCalendar dt
  ON a.orderDate = dt.orderDate
  WHERE a.orderDate IS NOT NULL
  GROUP BY CUBE (SaleID, state, dt.CumulativeWeek)
) src
pivot
(
  COUNT([Sales By State])
  For Week IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13])
) piv

我尝试将CUBE()语句移出我的数据透视表，但在这里收到编译错误，这是更新的代码

select *
FROM
(
  select case 
  WHEN a.state LIKE 'CA' THEN 'California'
  WHEN a.state LIKE 'WA' THEN 'Washington'
  else a.state
  end As [Full State], 
  SaleID As [Sales By State], 
  CONVERT(VARCHAR(20), dt.CumulativeWeek) AS Week
  FROM retailsales.Store1 a
  INNER JOIN retailsales.customCalendar dt
  ON a.orderDate = dt.orderDate
  WHERE a.orderDate IS NOT NULL
  GROUP BY CUBE (SaleID, state, dt.CumulativeWeek)
) src
pivot
(
  COUNT([Sales By State])
  For Week IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13])
) piv
GROUP BY CUBE (Full State)

并产生此编译错误

Msg 8120, Level 16, State 1, Line 1
Column 'piv.1' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

埃里克·布莱斯曼（Erik Blessman）

带有汇总行的UNION ALL将向您显示带有SUM的原始数据透视表

WITH data AS (
    select *
    FROM
    (
      select case 
      WHEN a.state LIKE 'CA' THEN 'California'
      WHEN a.state LIKE 'WA' THEN 'Washington'
      else a.state
      end As [Full State], 
      SaleID As [Sales By State], 
      CONVERT(VARCHAR(20), dt.CumulativeWeek) AS Week
      FROM retailsales.Store1 a
      INNER JOIN retailsales.customCalendar dt
      ON a.orderDate = dt.orderDate
      WHERE a.orderDate IS NOT NULL
      GROUP BY CUBE (SaleID, state, dt.CumulativeWeek)
    ) src
    pivot
    (
      COUNT([Sales By State])
      For Week IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13])
    ) piv
)
SELECT [Full State]
     , [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13]
FROM   data
UNION ALL
SELECT ''
     , SUM([1]),SUM([2]),SUM([3]),SUM([4]),SUM([5]),SUM([6])
     , SUM([7]),SUM([8]),SUM([9]),SUM([10]),SUM([11]),SUM([12]),SUM([13])
FROM   data