How do I use Pivot when there's a 'Totals' column?

Question

I put together a query that displays the number of wells/year and sums their totals in a column called, "Totals". Please see the db<>fiddle link at the bottom of the post for the working query.

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)

The query works but I'd like to transpose the table so the years are listed at the top as column names and the totals column is a row at the bottom.

Here is the table that is produced from the query above:

Year	Oil Wells	Gas Wells	Service Wells	Dry Holes	Totals
2017	6	7	1	1	15
2018	15	23	5	6	49
2019	7	6	4	5	22
2020	10	16	4	0	30
2021	24	23	3	3	53

Here is what I'd like to have:

Well Types	2021	2020	2019	2018	2017
Oil Wells	24	10	7	15	6
Gas Wells	23	16	6	23	7
Service Wells	3	4	4	5	1
Dry Holes	3	0	5	6	1
Totals	53	30	22	49	15

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.

Dose this answer your question? [Simple way to transpose columns and rows in SQL?](https://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql) — Thom A, Nov 08 '21 at 15:00
Side note, on the DB<>Fiddle, you are aware that SQL Server supports multiple rows being inserted in a single statement right? 907 separate `INSERT` statements to insert 1 row each is significantly inefficient compared to a single `INSERT` that inserts 907. — Thom A, Nov 08 '21 at 15:11
By significantly, I do mean **significantly**, by the way. On my local instance, a single `INSERT` with 907 rows runs almost 25 times *faster*. — Thom A, Nov 08 '21 at 15:15
@Larnu I don't know an easy way to export a sampling of my data from sql-server, this was about as good as I could get it without doing a lot of manual editing. Unfortunately, it included all of the `INSERT` statements. Does anyone with the link have permission to remove them? I'm also looking at your link right now. — Hiebs915, Nov 08 '21 at 15:19

Charlieface · Answer 1 · 2021-11-08T21:09:55.370

You are approaching this wrong. Instead of pivoting your existing query you should be just using conditional aggregation.

Note other efficiencies

No need to join multiple times. Just join once and use CASE expressions
Note the use of CROSS APPLY (VALUES to create and re-use the grouping expression
Use GROUPING SETS or ROLLUP to get the totals row, use the GROUPING() function to identify that row
Do not use functions on columns in the WHERE, instead create the date range you need and filter on that.
If you need dynamic years, instead of using dynamic SQL, just call the columns ThisYear LastYear etc

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
;

db<>fiddle

When you say, "Do not use functions on columns in the WHERE, instead create the date range you need and filter on that." why shouldn't I do this? — Hiebs915, Nov 08 '21 at 18:37
@Hiebs915 It's just that the query optimizer could ignore the index or partitioning on a field if the criteria wraps the field in a function. Thus potentionally make the query slower. It doesn't matter much when the field isn't partitioned or indexed. But some then avoid it anyway, just in case. — LukStorms, Nov 08 '21 at 19:04
@Hiebs915 Because `YEAR(someDate` simply can't use indexes. SQL Server has no mechanism to see that you really meant "from the beginning of this year until the end of that year" it will just calculate the `YEAR` function on every single row in the table. See also https://www.brentozar.com/archive/2016/12/date-math-clause/ — Charlieface, Nov 08 '21 at 20:17
@Charlieface I ran this on my full set of data and it looks good except an extra row is added. The WellType for the row is NULL and it shows 2021=0, 2020=0, 2019=0, 2018=2, and 2-17=3. I know you don't have the full set of data so the results might be diff. for me but do you have any ideas? — Hiebs915, Nov 08 '21 at 21:02
Yes, it probably represents all rows with `w.WellType` not any of the options in the `CASE`. You need a `WHERE`, have modified. Either that or add `ELSE 'Other'` to the `CASE` — Charlieface, Nov 08 '21 at 21:09

score 1 · Accepted Answer · edited Nov 08 '21 at 18:45

If you first group by the well type and the year, then it becomes easier to pivot in the outer query.

Since the Hole Category can already be calculated in the sub-query.

And by grouping with a rollup and conditional sums, it'll also gets the totals.

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

could I use GETDATE() for the column years so that it updates automatically? I think dynamic SQL would be needed for this. Ideally, I'd prefer not to use "This Year", "Last Year", etc. because most people that look at this data want the year in number format. — Hiebs915, Nov 08 '21 at 17:00
@Hiebs915 What you do with the solutions offered to you is totally up to you. But having years as column names is typically something that requires dynamic sql. Sucks, but that's how it is. The year criteria were hardcoded because it's small effort to change them when the column names need to be changed. And if you transform the sql to dynamic sql, then you could change the numbers then anyway. — LukStorms, Nov 08 '21 at 17:17
Ok. The query results will eventually be shown as a table with PHP. Maybe I can use PHP to set the column names as dynamic years, that way I don't have to mess with dynamic sql. — Hiebs915, Nov 08 '21 at 18:34
I had to add an additional line to the `WHERE` clause so it only sums the wanted well types. `AND w.WellType IN ('OW','GW','WI','GI','GS','WD','WS','TW','D')`. Thanks to @Charlieface for this. — Hiebs915, Nov 08 '21 at 21:23

How do I use Pivot when there's a 'Totals' column?

2 Answers2