有“總計”列時如何使用Pivot？

我整理了一個查詢，該查詢顯示了/年的井數，并在名為“總計”的列中匯總了它們的總數。有關作業查詢，請參閱帖子底部的 db<>fiddle 鏈接。

SELECT
    YEAR(wd.eventdate) AS [Year],
    ISNULL(COUNT(DISTINCT w_oil.WellID), 0) AS [Oil Wells],
    ISNULL(COUNT(DISTINCT w_gas.WellID), 0) AS [Gas Wells],
    ISNULL(COUNT(DISTINCT w_service.WellID), 0) AS [Service Wells],
    ISNULL(COUNT(DISTINCT w_dry.WellID), 0) AS [Dry Holes],
    ISNULL(COUNT(DISTINCT w_tot.WellID), 0) AS [Totals]
FROM HWellDate wd
    LEFT JOIN HWell w_oil ON (w_oil.PKey = wd.WellKey and w_oil.WellType = 'OW')
    LEFT JOIN HWell w_gas ON (w_gas.PKey = wd.WellKey and w_gas.WellType = 'GW')
    LEFT JOIN HWell w_service ON
    (
    w_service.PKey = wd.WellKey
    AND w_service.WellType IN ('WI','GI','GS','WD','WS','TW')
    )
    LEFT JOIN HWell w_dry ON (w_dry.PKey = wd.WellKey and w_dry.WellType = 'D')
    LEFT JOIN HWell w_tot ON w_tot.PKey = wd.WellKey
WHERE wd.comment = 'PA'
    AND wd.event = 'WELLSTATUS'
    AND (YEAR(wd.eventdate) >= (YEAR(GETDATE()) - 4) AND YEAR(wd.eventdate) <= YEAR(GETDATE()))
GROUP BY YEAR(wd.eventdate)

查詢有效，但我想轉置表格，以便年份作為列名列在頂部，總計列是底部的一行。

這是從上面的查詢生成的表：

這是我想要的：

I think I need to use PIVOT to rotate the table but suspect I may need to use UNPIVOT as well to get the results I'm looking for. I was thinking I could insert the data from the first table into a temp table called, "#wellsPluggedTempTbl". After that, maybe I could use dynamic sql to generate the results.

Here's what I have so far:

DECLARE @colsPivot AS NVARCHAR(MAX)
DECLARE @query  AS NVARCHAR(MAX)

INSERT INTO #wellsPluggedTempTbl([Year], [Oil Wells], [Gas Wells], [Service Wells], [Dry Holes], Totals)
SELECT
    YEAR(wd.eventdate) AS [Year],
    ISNULL(COUNT(DISTINCT w_oil.WellID), 0) AS [Oil Wells],
    ISNULL(COUNT(DISTINCT w_gas.WellID), 0) AS [Gas Wells],
    ISNULL(COUNT(DISTINCT w_service.WellID), 0) AS [Service Wells],
    ISNULL(COUNT(DISTINCT w_dry.WellID), 0) AS [Dry Holes],
    ISNULL(COUNT(DISTINCT w_tot.WellID), 0) AS [Totals]
FROM HWellDate wd
    LEFT JOIN HWell w_oil ON (w_oil.PKey = wd.WellKey and w_oil.WellType = 'OW')
    LEFT JOIN HWell w_gas ON (w_gas.PKey = wd.WellKey and w_gas.WellType = 'GW')
    LEFT JOIN HWell w_service ON
    (
    w_service.PKey = wd.WellKey
    AND w_service.WellType IN ('WI','GI','GS','WD','WS','TW')
    )
    LEFT JOIN HWell w_dry ON (w_dry.PKey = wd.WellKey and w_dry.WellType = 'D')
    LEFT JOIN HWell w_tot ON w_tot.PKey = wd.WellKey
WHERE wd.comment = 'PA'
    AND wd.event = 'WELLSTATUS'
    AND (YEAR(wd.eventdate) >= (YEAR(GETDATE()) - 4) AND YEAR(wd.eventdate) <= YEAR(GETDATE()))
GROUP BY YEAR(wd.eventdate)

However, I get this error after running the above: "Invalid object name '#wellsPluggedTempTbl'.

For the well type ordering at the end, I know I need to use a CASE WHEN statement like this:

ORDER BY
    CASE WellType
        WHEN 'Totals' THEN 5
        WHEN 'Dry Holes' THEN 4
        WHEN 'Service Wells' THEN 3
        WHEN 'Gas Wells' THEN 2
        WHEN 'Oil Wells' THEN 1
    END

Here is a link to db<>fiddle where I have a sampling of the data that produces the results in this post. Any help would be appreciated! Thanks.

uj5u.com熱心網友回復：

你正在接近這個錯誤。您應該只使用條件聚合，而不是旋轉現有查詢。

注意其他效率

• 無需多次加入。只需加入一次并使用CASE運算式
• 注意CROSS APPLY (VALUES創建和重用分組運算式的使用
• 使用GROUPING SETS或ROLLUP獲取總計行，使用該GROUPING()函式來標識該行
• 不要在 中的列上使用函式WHERE，而是創建您需要的日期范圍并對其進行過濾。
• 如果您需要動態年份，而不是使用動態 SQL，只需呼叫列ThisYear LastYear等

SELECT
  WellType = CASE WHEN GROUPING(v.WellType) = 0 THEN v.WellType ELSE 'Totals' END,
  [2021] = COUNT(CASE WHEN [Year] = 2021 THEN 1 END),
  [2020] = COUNT(CASE WHEN [Year] = 2020 THEN 1 END),
  [2019] = COUNT(CASE WHEN [Year] = 2019 THEN 1 END),
  [2018] = COUNT(CASE WHEN [Year] = 2018 THEN 1 END),
  [2017] = COUNT(CASE WHEN [Year] = 2017 THEN 1 END)
FROM HWellDate wd
JOIN HWell w ON w.PKey = wd.WellKey
CROSS APPLY (VALUES(
    CASE WHEN w.WellType = 'OW' THEN 'Oil Wells'
         WHEN w.WellType = 'GW' THEN 'Gas Wells'
         WHEN w.WellType IN ('WI','GI','GS','WD','WS','TW') THEN 'Service Wells'
         WHEN w.WellType = 'D' THEN 'Dry Holes'
    END,
    YEAR(wd.eventdate)
)) v(WellType, Year)
WHERE wd.comment = 'PA'
  AND wd.event = 'WELLSTATUS'
  AND wd.eventdate >= DATEFROMPARTS(YEAR(GETDATE()) - 4, 1, 1)
  AND wd.eventdate <  DATEFROMPARTS(YEAR(GETDATE())   1, 1, 1)
  AND w.WellType IN ('OW','GW','WI','GI','GS','WD','WS','TW','D')
GROUP BY GROUPING SETS (
    (v.WellType),
    ()
 )
ORDER BY GROUPING(v.WellType) DESC,
    CASE v.WellType
        WHEN 'Dry Holes' THEN 4
        WHEN 'Service Wells' THEN 3
        WHEN 'Gas Wells' THEN 2
        WHEN 'Oil Wells' THEN 1
    END
;

資料庫<>小提琴

uj5u.com熱心網友回復：

如果您首先按井型別和年份分組，那么在外部查詢中進行透視會變得更容易。

由于孔類別已經可以在子查詢中計算出來。

并且通過匯總和條件總和分組，它還將獲得總數。

SELECT ISNULL(q.HoleCategory, 'Total') AS WellType
, ISNULL(SUM(CASE WHEN q.eventYear=2021 THEN q.Total END),0) AS [2021]
, ISNULL(SUM(CASE WHEN q.eventYear=2020 THEN q.Total END),0) AS [2020]
, ISNULL(SUM(CASE WHEN q.eventYear=2019 THEN q.Total END),0) AS [2019]
, ISNULL(SUM(CASE WHEN q.eventYear=2018 THEN q.Total END),0) AS [2018]
, ISNULL(SUM(CASE WHEN q.eventYear=2017 THEN q.Total END),0) AS [2017]
FROM
(
    SELECT w.WellType
    , YEAR(wd.eventDate) AS eventYear
    ,CASE 
     WHEN w.WellType = 'OW' THEN 'Oil Wells'
     WHEN w.WellType = 'GW' THEN 'Gas Wells'
     WHEN w.WellType IN ('WI','GI','GS','WD','WS','TW') THEN 'Service Wells'
     WHEN w.WellType = 'D' THEN 'Dry Holes'
     END AS HoleCategory 
    , COUNT(DISTINCT w.WellID) AS Total
    FROM HWellDate wd
    LEFT JOIN HWell w ON w.PKey = wd.WellKey
    WHERE wd.comment = 'PA'
      AND wd.event = 'WELLSTATUS'
      AND w.WellType IS NOT NULL
      AND YEAR(wd.eventdate) BETWEEN 2017 AND 2021
    GROUP BY w.WellType, YEAR(wd.eventDate)
) q
GROUP BY ROLLUP(q.HoleCategory)
ORDER BY
    CASE q.HoleCategory
    WHEN 'Oil Wells' THEN 1
    WHEN 'Gas Wells' THEN 2
    WHEN 'Service Wells' THEN 3
    WHEN 'Dry Holes' THEN 4
    ELSE 9
    END

標籤：sql sql-server sql-server-2012 pivot unpivot

上一篇：如何從同一列SQLServer中的不同值組求和（MAX值）

下一篇：雙子串