SQL Pivot via CTE

Transact-SQL
1

Hope someone can help, I don't even know if what I want is possible.

I have the following code

WITH CTE AS
(
SELECT *, ISNULL ([JAN], 0) + ISNULL ([FEB], 0) + ISNULL ([MAR], 0) + ISNULL ([APR], 0) + ISNULL ([MAY], 0) + ISNULL ([JUN], 0) + ISNULL ([JUL], 0) + ISNULL ([AUG], 0) + ISNULL ([SEP], 0) + ISNULL ([OCT], 0) + ISNULL ([NOV], 0) + ISNULL ([DEC], 0) AS TOTAL FROM 
(SELECT CASE
WHEN MONTH ([DateInstructionReceived]) = '1' THEN 'JAN'
WHEN MONTH ([DateInstructionReceived]) = '2' THEN 'FEB'
WHEN MONTH ([DateInstructionReceived]) = '3' THEN 'MAR'
WHEN MONTH ([DateInstructionReceived]) = '4' THEN 'APR'
WHEN MONTH ([DateInstructionReceived]) = '5' THEN 'MAY'
WHEN MONTH ([DateInstructionReceived]) = '6' THEN 'JUN'
WHEN MONTH ([DateInstructionReceived]) = '7' THEN 'JUL'
WHEN MONTH ([DateInstructionReceived]) = '8' THEN 'AUG'
WHEN MONTH ([DateInstructionReceived]) = '9' THEN 'SEP'
WHEN MONTH ([DateInstructionReceived]) = '10' THEN 'OCT'
WHEN MONTH ([DateInstructionReceived]) = '11' THEN 'NOV'
WHEN MONTH ([DateInstructionReceived]) = '12' THEN 'DEC'
END AS [FileDate],
[CaseNumber],
[Solicitor]
FROM [dbo].[Instructions]
WHERE [LoadedDate] >= (SELECT CAST (GETDATE() AS DATE))
AND YEAR ([DateInstructionReceived]) = '2018') AS i
 
PIVOT
(
COUNT (CaseNumber)
FOR [FileDate] IN (JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC) 
)

AS pvt
)
SELECT * FROM CTE
UNION
SELECT 'ZZTOTAL', SUM (JAN), SUM (FEB), SUM (MAR), SUM (APR), SUM (MAY), SUM (JUN), SUM (JUL), SUM (AUG), SUM (SEP), SUM (OCT), SUM (NOV), SUM (DEC), SUM (TOTAL)
FROM CTE

This works perfectly.

What I'd like to do is add in another column to the CTE (product) and then be able to pivot on both solicitor and product (as you'd be able to do easily in Excel).

Any guidance appreciated as always.

2

For the first part, why not:

(SELECT CASE MONTH ([DateInstructionReceived])
WHEN '1' THEN 'JAN'
WHEN '2' THEN 'FEB'
WHEN '3' THEN 'MAR'
WHEN '4' THEN 'APR'
WHEN '5' THEN 'MAY'
WHEN '6' THEN 'JUN'
WHEN '7' THEN 'JUL'
WHEN '8' THEN 'AUG'
WHEN '9' THEN 'SEP'
WHEN '10' THEN 'OCT'
WHEN '11' THEN 'NOV'
WHEN '12' THEN 'DEC'
END AS [FileDate],

or even just

CONVERT(char(3), DateInstructionReceived, 100) AS FileDate

As to your actual question, You can't pivot on two columns at once. However, could you make a composite of Solicitor and Product in your CTE and pivot on that derived column?

1 Like
3

Thanks for the answer and the code optimisation, much appreciated.

I hadn't considered a composite option - if pivoting two colums at once is not possible in SQL then your answer is the way to go.

Thanks again.