Combining two T-SQL pivot queries in one

Question

Suppose you had this table:

CREATE TABLE Records
(
    RecordId       int IDENTITY(1,1) NOT NULL,
    CreateDate     datetime          NOT NULL,
    IsSpecial      bit               NOT NULL
    CONSTRAINT PK_Records   PRIMARY KEY(RecordId)
)

Now a report needs to be created where the total records and the total special records are broken down by month. I can use these two queries separately:

--      TOTAL RECORDS PER MONTH
SELECT January, February, March, April, May, June,
    July, August, September, October, November, December
FROM (
    SELECT RecordId, DATENAME(MONTH, CreateDate) AS RecordMonth
    FROM dbo.Records
) AS SourceTable
PIVOT (
    COUNT(RecordId) FOR RecordMonth IN (January, February, March, April, May, June,
    July, August, September, October, November, December)
) AS PivotTable;

--      TOTAL SPECIAL RECORDS PER MONTH
SELECT January, February, March, April, May, June,
    July, August, September, October, November, December
FROM (
    SELECT RecordId, DATENAME(MONTH, CreateDate) AS RecordMonth
    FROM dbo.Records
    WHERE IsSpecial = 1
) AS SourceTable
PIVOT (
    COUNT(RecordId) FOR RecordMonth IN (January, February, March, April, May, June,
    July, August, September, October, November, December)
) AS PivotTable;

The results might look like this:

                  Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec
total              0     0    2     2     1      0     0    1      2     1     2    4
total special      0     0    1     0     1      0     0    0      0     0     0    2

Is it possible to combine these two queries into a single more efficient query?

Answer 1

You can only have one aggregate (COUNT(RecordId)) per pivot so all you do is combine into one result set with a UNION ALL with a suitable extra column to identify each pivot.

Otherwise, you have no way to distinguish the 2 different aggregates in the pivot

Answer 2

I would do it like this:

SELECT
     CASE SQ.total_type
          WHEN 1 THEN 'total special'
          WHEN 2 THEN 'total expensive'
          ELSE 'total'
     END AS total_type,
     SUM(CASE WHEN MONTH(R.CreateDate) = 1 THEN 1 ELSE 0 END) AS January,
     SUM(CASE WHEN MONTH(R.CreateDate) = 2 THEN 1 ELSE 0 END) AS February,
     SUM(CASE WHEN MONTH(R.CreateDate) = 3 THEN 1 ELSE 0 END) AS March,
     ...
FROM
     dbo.Records R
INNER JOIN
     (
          SELECT 0 AS total_type UNION ALL   -- All
          SELECT 1 UNION ALL                 -- IsSpecial
          SELECT 2                           -- IsExpensive
     ) AS SQ ON
     (R.IsSpecial | (R.IsExpensive * 2)) & SQ.total_type = SQ.total_type
GROUP BY
     SQ.total_type
ORDER BY
     SQ.total_type DESC

Answer 3

Thanks for the solution Tom, that answers my pivot question.

Too bad for me I had the wrong question. For my problem I'm now feeling it would be better to use a plain grouping query like this instead:

SELECT DATENAME(MONTH, CreateDate) AS Month,
    COUNT(*) AS Total,
    SUM(CASE
        WHEN IsSpecial = 1 THEN 1
        ELSE 0
    END) AS TotalSpecial,
    SUM(CASE
        WHEN IsExpensive = 1 THEN 1
        ELSE 0
    END) AS TotalExpensive
FROM Records
GROUP BY DATENAME(MONTH, CreateDate);

Then all that is left to do is rotate the results before they are presented. Nice to know eh?