Date: 10/10/07 (SQL Server)    Keywords: no keywords

Okay, not quite understanding what I'm doing wrong. According to the help file, this query:

-- Pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost
FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable

should produce this:

Cost_Sorted_By_Production_Days 0 1 2 3 4
AverageCost 5.0885 223.88 359.1082 NULL 949.4105

I translate it to work with my own data set.

select division_sysno, division_desc, upc_sysno, upc_code, item_sysno, item_code, item_desc,
revlon_product, company_category_sysno, [286]...[389]
from @m_SkeletonData
pivot
(
sum(total_units)
for period_sysno
in ([286]...[389])
) as p

(101 numbers removed for brevity from each bit)

I should get one row for each product, yes? Ah, no! Here's a heavily-edited sample of my result set:

1 WEST COAST 1376418 03300000013 147303 0409076 ALMAY 1 COAT L/S FLUTTER Almay 189049 13 NULL NULL NULL
1 WEST COAST 1376418 03300000013 147303 0409076 ALMAY 1 COAT L/S FLUTTER Almay 189049 NULL 4 NULL NULL
1 WEST COAST 1376418 03300000013 147303 0409076 ALMAY 1 COAT L/S FLUTTER Almay 189049 NULL NULL 10 NULL
1 WEST COAST 1376418 03300000013 147303 0409076 ALMAY 1 COAT L/S FLUTTER Almay 189049 NULL NULL NULL 10

Obviously, it's not aggregating the way I expect it to. Given that the rest of the information on each line is identical, I can't quite understand why. I thought it might be the extra non-aggregated fields, but I reduced it to a single field of that type, and the problem persists. Anyone have any ideas? Thanks.

Source: http://community.livejournal.com/sqlserver/63976.html