Hi Alan,

You need to change your table to a subquery involving only the grouping columns and the ones to be aggregated.

SELECT [1] AS q1, [2] AS q2, [3] AS q3, [4] AS q4

FROM (SELECT qtr, sales FROM #p) AS p

PIVOT(

SUM(sales)

FOR qtr IN ([1],[2],[3],[4])

) AS pivottbl;

Why wouldn't you want to use a nice pre-aggregated cross tab approach?

SELECT

SUM(CASE qtr WHEN 1 THEN sales END) AS q1,

SUM(CASE qtr WHEN 2 THEN sales END) AS q2,

SUM(CASE qtr WHEN 3 THEN sales END) AS q3,

SUM(CASE qtr WHEN 4 THEN sales END) AS q4

FROM (SELECT qtr, SUM(sales) sales

FROM #p

GROUP BY qtr)p;