时间戳

首页 > 解决方案 > MS SQL PIVOT MONTH, QUARTER AND YEAR - 如果存在类似月份值，则显示错误数据的季度

sql-server - MS SQL PIVOT MONTH, QUARTER AND YEAR - 如果存在类似月份值，则显示错误数据的季度

问题描述

我在表中有以下数据结构。

UNITDATE    UNIT
2020-01-01  550.00
2020-01-01  777.00
2020-02-01  887.00
2020-02-01  111.00
2020-03-01  501.00
2020-03-01  500.00
2020-04-01  516.00
2020-05-01  516.00
2020-06-01  723.00

但以下数据透视查询返回错误的季度 SUM(Q2) 结果，因为 2020-04-01 和 2020-05-01 具有相同的值。

SELECT SUM([1]) AS Jan, SUM([2]) AS Feb, SUM([3]) AS Mar,SUM(Q1) AS Q1,  
                SUM([4]) AS Apr, SUM([5]) AS May, SUM([6]) AS Jun, SUM(Q2) AS Q2, 
                SUM([7]) AS Jul, SUM([8]) AS Aug, SUM([9]) AS Sep, SUM(Q3) AS Q3,
                SUM([10]) AS Oct, SUM([11]) AS Nov, SUM([12]) AS Dec, SUM(Q4) AS Q4, SUM([2020]) AS YEARLY 
        FROM (SELECT  
        DATEPART(MONTH, UNITDATE) as month,
        CAST('Q' + CAST(DATEPART(QUARTER, UNITDATE) AS VARCHAR(1)) AS VARCHAR(2)) AS quarter,
        DATEPART(YEAR, UNITDATE) as year,
        SUM(UNIT) AS M,
        SUM(UNIT) AS Q,
        SUM(UNIT) AS Y FROM MyTable WHERE DATEPART(YEAR, UNITDATE) = 2020 GROUP BY UNITDATE) as yearData 
        PIVOT (SUM(M) FOR month IN ( [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS PM     
        PIVOT (SUM(Q) FOR quarter IN ([Q1],[Q2],[Q3],[Q4])) AS PQ
        PIVOT (SUM(Y) FOR year IN ([2020])) AS PY;

Jan Feb Mar Q1  Apr May Jun Q2  Jul Aug Sep Q3  Oct Nov Dec Q4  YEARLY
1327.00 998.00  1001.00 3326.00 516.00  516.00  723.00  1239.00 NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    4565.00

有人遇到类似的问题，请指教。

标签： sql-serversql-server-2008pivot-tableunpivot

解决方案

不要使用PIVOT运算符，它是限制性和笨重的。条件聚合会容易得多：

WITH YourData AS(
    SELECT *
    FROM(VALUES(CONVERT(date,'2020-01-01'),550.00),
               (CONVERT(date,'2020-01-01'),777.00),
               (CONVERT(date,'2020-02-01'),887.00),
               (CONVERT(date,'2020-02-01'),111.00),
               (CONVERT(date,'2020-03-01'),501.00),
               (CONVERT(date,'2020-03-01'),500.00),
               (CONVERT(date,'2020-04-01'),516.00),
               (CONVERT(date,'2020-05-01'),516.00),
               (CONVERT(date,'2020-06-01'),723.00))V(UNITDATE,UNIT))
SELECT SUM(CASE DATEPART(MONTH,UNITDATE) WHEN 1 THEN UNIT END) AS Jan,
       SUM(CASE DATEPART(MONTH,UNITDATE) WHEN 2 THEN UNIT END) AS Feb,
       SUM(CASE DATEPART(MONTH,UNITDATE) WHEN 3 THEN UNIT END) AS Mar,
       SUM(CASE WHEN DATEPART(MONTH,UNITDATE) BETWEEN 1 AND 3 THEN UNIT END) AS Q1,
       SUM(CASE DATEPART(MONTH,UNITDATE) WHEN 4 THEN UNIT END) AS Apr,
       -- You get the idea
       SUM(UNIT) AS Yearly
FROM YourData
WHERE UNITDATE >= '20200101'
  AND UNITDATE < '20210101';

推荐阅读