SQL Server pivot does not group

Question

I am reading the prep kit for MS exam 70-461 : Querying MS SQL Server 2012 and the author talks about the PIVOT operator. I am using the AdventureWorks sample database to make tests.

I decided to do a simple test and see if I could present the average base rate per department on columns split for each gender (M, F).

I have the following:

WITH CTE AS
(
    SELECT 
        [DepartmentName], 
        [Gender], 
        [BaseRate] 
    FROM 
        [AdventureWorks].[dbo].[DimEmployee]
)

SELECT
    [DepartmentName], [M], [F]
FROM 
    [AdventureWorks].[dbo].[DimEmployee] PIVOT (Avg(BaseRate) FOR [Gender] IN ([M], [F])) AS P

However, I noticed this:

There are no groups on [DepartmentName] as should be (according to the book) since it's the only field not specified in the PIVOT operator.

I can add a simple GROUP BY clause but I am not sure that this would yield the appropriate result and this also means that I did not correctly understand the use of PIVOT.

Any idea where I am wrong ?

Answer 1

You define the CTE but then ignore it and select from the base table anyway.

It should be

WITH CTE
     AS (SELECT [DepartmentName],
                [Gender],
                [BaseRate]
         FROM   [AdventureWorks].[dbo].[DimEmployee])
SELECT [DepartmentName],
       [M],
       [F]
FROM   CTE PIVOT (Avg(BaseRate) FOR [Gender] IN ([M], [F])) AS P