The PIVOT statement can be useful at times, but it is handy to know that anything that can be done with a PIVOT statement can be converted to an equivalent query without the PIVOT statement.

Here is how:

1. All non-pivoted columns from the source query are put in a GROUP BY clause (e.g. GROUP BY salesPersonsName)

2. Create a case statement for each pivoted column value (e.g. Jan, Feb) that returns the column to be aggregated when the column to be pivoted matches the value. For example

CASE WHEN monthAbv = 'Jan' THEN numberOfSales END

3. Wrap each case statement in the desired aggregate function and add the column alias. Note that this also gives you flexibility to name the columns whatever you want. For example

SUM(CASE WHEN monthAbv = 'Jan' THEN numberOfSales END) AS [Jan]

For more details and more examples see the comments in the script below.

-- The initial PIVOT query:

select salesPersonsName, [Jan], [Feb] from

(select salesPersonsName, numberOfSales, monthAbv from #tmpPivotTest) as SourceTable

--------^ salesPersonsName is the only non-pivoted column from the source;

-------- so we GROUP BY salesPersonsName

PIVOT (sum(numberOfSales) for monthAbv in ([Jan],[Feb]) )as PivotTable

-------^ sum is the Aggregate function ----^ Jan and Feb are the values to use in the case statements

-- The equivalent CASE/GROUP BY query:

SELECT

salesPersonsName,

SUM(CASE WHEN monthAbv = 'Jan' THEN numberOfSales END) AS Jan,

SUM(CASE WHEN monthAbv = 'Feb' THEN numberOfSales END) AS Feb

FROM #tmpPivotTest

GROUP BY salesPersonsName

-- Pivot table query trying to get saver points as well:

select salesPersonsName, [Jan], [Feb], saverPoints from

(select * from #tmpPivotTest) as SourceTable

--------^ salesPersonsName and saverPoints are both non-pivoted columns returned by the source query;

-------- so we GROUP BY salesPersonsName, saverPoints

PIVOT (sum(numberOfSales) for monthAbv in ([Jan], [Feb]) )as PivotTable

-------^ sum is the Aggregate function ----^ Jan and Feb are the values to use in the case statements

-- The equivalent CASE/GROUP BY query:

SELECT

salesPersonsName,

SUM(CASE WHEN monthAbv = 'Jan' THEN numberOfSales END) AS Jan,

SUM(CASE WHEN monthAbv = 'Feb' THEN numberOfSales END) AS Feb,

saverPoints

FROM #tmpPivotTest

GROUP BY salesPersonsName, saverPoints

-- The desired results with the sum of saverPoints using CASE/GROUP BY

SELECT

salesPersonsName,

SUM(CASE WHEN monthAbv = 'Jan' THEN numberOfSales END) AS Jan,

SUM(CASE WHEN monthAbv = 'Feb' THEN numberOfSales END) AS Feb,

SUM(saverPoints) AS saverPoints

FROM #tmpPivotTest

GROUP BY salesPersonsName

-- Note that CASE/GROUP BY is more flexible.

-- PIVOT only allows one source column to be aggregated.

-- For example, to aggregate both sales and points by month:

SELECT

salesPersonsName,

SUM(CASE WHEN monthAbv = 'Jan' THEN numberOfSales END) AS [Jan Sales],

SUM(CASE WHEN monthAbv = 'Jan' THEN saverPoints END) AS [Jan Points],

SUM(CASE WHEN monthAbv = 'Feb' THEN numberOfSales END) AS [Feb Sales],

SUM(CASE WHEN monthAbv = 'Feb' THEN saverPoints END) AS [Feb Points]

FROM #tmpPivotTest

GROUP BY salesPersonsName

-- In order to do the equivalent of the above query you would need to join two PIVOT

-- statements, which is less efficient because it queries the source table twice.

;with SalesPivotResults as

(

select salesPersonsName, [Jan] as [Jan Sales], [Feb] as [Feb Sales]

from (select salesPersonsName, numberOfSales, monthAbv from #tmpPivotTest)as SalesSourceTable

PIVOT (sum(numberOfSales) for monthAbv in ([Jan],[Feb]) )as SalesPivotTable

), PointsPivotResults as

(

select salesPersonsName, [Jan] as [Jan Points], [Feb] as [Feb Points]

from (select salesPersonsName, saverPoints, monthAbv from #tmpPivotTest)as PointsSourceTable

PIVOT (sum(saverPoints) for monthAbv in ([Jan],[Feb]) )as PointsPivotTable

)

select SalesPivotResults.salesPersonsName, [Jan Sales], [Jan Points], [Feb Sales], [Feb Points]

from SalesPivotResults

INNER JOIN PointsPivotResults ON PointsPivotResults.salesPersonsName = SalesPivotResults.salesPersonsName