带日历表的枢轴

Question

我试图将这些数据转换为支点，但我得到的错误是

Msg 102, Level 15, State 1, Line 16
Incorrect syntax near ','.

在SUM([Total Count])之后突出显示逗号，但它必须在那里，我应该更改什么才能正确地执行查询？

select *
FROM
(
  select a.regionalLocale As [RL], 
  Count(ID) As [Total Count], 
  CONVERT(VARCHAR(20), dt.week) AS Week
  FROM database14.dataTable a
  INNER JOIN calendarDB.masterCalendar dt
  ON a.SaleDate = dt.FullDate
  WHERE a.SaleDate IS NOT NULL
) src
pivot
(
  SUM([Total Count]), [RL]
  For Week IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13])
) piv

Answer 1

您的查询有一些问题。

一种是在枢轴中使用语法SUM([Total Count]), [RL]。您只想包含您在这里聚合的列。

第二，没有必要在您的子查询中使用count(id)，让支点聚合处理总数。将代码更改为：

select [RL], 
  [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13]
FROM
(
  select a.regionalLocale As [RL], 
    ID, 
    CONVERT(VARCHAR(20), dt.week) AS Week
  FROM database14.dataTable a
  INNER JOIN calendarDB.masterCalendar dt
    ON a.SaleDate = dt.FullDate
  WHERE a.SaleDate IS NOT NULL
) src
pivot
(
  COUNT(ID)
  For Week IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13])
) piv