I have a nested unionized query that's structured like below which is essentially two of the query structures from this solution: Using pivot table with column and row totals in sql server 2008

Is it possible to combine the two column subtotal rows between group by rollup queries?

SELECT
  [Line] = ISNULL(line_name, 'Total'),
  [A] = SUM([A]),
  [B] = SUM([B]),
  [C] = SUM([C]),
  Total = SUM([A] [B] [C])
FROM (
  SELECT line_name, stage_name
  FROM table1 a 
  INNER JOIN table2 b 
      ON a...=b...
  INNER JOIN table3 c 
      ON a...=c...
  WHERE line_name LIKE '%pattern1%'
) s1
PIVOT (
  COUNT(stage_name)
  FOR stage_name IN ([A],[B],[C])
) p1
GROUP BY
  ROLLUP(line_name)
UNION ALL
SELECT
  [Line] = ISNULL(line_name, 'Total'),
  [A] = SUM([A]),
  [B] = SUM([B]),
  [C] = SUM([C]),
  Total = SUM([A] [B] [C])
FROM (
  SELECT line_name, stage_name
  FROM table1 a 
  INNER JOIN table2 b 
      ON a...=b...
  INNER JOIN table3 c 
      ON a...=c...
  INNER JOIN table4 d 
      ON b...=d...
  WHERE line_name LIKE '%pattern2%'
) s1
PIVOT (
  COUNT(stage_name)
  FOR stage_name IN ([A],[B],[C])
) p2
GROUP BY
  ROLLUP(line_name)
;

Actual Results:

Desired Results:

I've tried nesting the above query but I'm not sure how to manipulate it past this point to add column subtotals since I can't use GROUP BY ROLLUP with this outer layer. Totals from a UNION query

SELECT pt.[Line],pt.[A],pt.[B],pt.[C]
FROM (
*above query*
) AS pt
GROUP BY pt.[Line],pt.[A],pt.[B],pt.[C]

CodePudding user response：

Seems like you want to UNION ALL before pivoting, in a derived/nested SELECT, then grouping and pivoting that.

Having said that, it's probably easier to use COUNT(CASE rather than PIVOT anyway

SELECT
  Line = CASE WHEN GROUPING(line_name) = 1 THEN 'Total' ELSE line_name END,  -- deals with nulls
  A = COUNT(CASE WHEN stage_name = 'A' THEN 1 END),
  B = COUNT(CASE WHEN stage_name = 'B' THEN 1 END),
  C = COUNT(CASE WHEN stage_name = 'C' THEN 1 END),
  Total = COUNT(*)
FROM (
    SELECT line_name, stage_name
    FROM table1 a 
    INNER JOIN table2 b 
        ON a...=b...
    INNER JOIN table3 c 
        ON a...=c...
    WHERE line_name LIKE '%pattern1%'

    UNION ALL

    SELECT line_name, stage_name
    FROM table1 a 
    INNER JOIN table2 b 
        ON a...=b...
    INNER JOIN table3 c 
        ON a...=c...
    INNER JOIN table4 d 
        ON b...=d...
    WHERE line_name LIKE '%pattern2%'
) s

WHERE stage_name IN ('A', 'B', 'C')  -- do you need this also, to filter out other values?
        -- ... it might improve perf, also needed to get COUNT(*) to work correctly

GROUP BY
  ROLLUP(line_name)
;