What is the key property that must be set in order to get the Pivot
transformation to aggregate rows rather than just display the same detail as
the input with lots of nulls in the output?

I want to create a pivot table that has the row headers showing Make of car,
column headers showing the years 1990, 1991 and 1992. The crucial thing I
want is to have one row only for each make of car. My source data is here:

Make Year Sales
-------------------- ----------- -----------
Honda 1990 2000
Honda 1990 1000
Acura 1990 500
Honda 1991 3000
Acura 1991 300
Acura 1991 600
Acura 1992 800

The output should be like this (what I get with the PIVOT operator in T-SQL):
Make 1990 1991 1992
-------------------- ----------- ----------- -----------
Acura 500 900 800
Honda 3000 3000 NULL

Instead, I am getting this:
Make [1990] [1991] [1992]
Acura 500 NULL NULL
Honda 3000 NULL NULL
Acura NULL 900 NULL
Honda NULL 3000 NULL
Acura NULL NULL 800

Books Online is very sketchy on this topic, and it suggests I sort on the
pivot column. Well, I sorted the incoming data on Year and I used the Sort
transformation to sort by Year so that the Input Columns IsSorted property
changed to True. (By the way, the IsSorted property never shows True unless
you precede the Pivot transformation with a Sort transformation). I have
also tried various permutations of the SortKeyPosition property on the output
columns, with no luck. What is the correct way of getting the desired result?
My source query for the package data flow was (also tried order by Make):

select Make, Year, sum(Sales) AS Sales
FROM dbo.CarSales group by Make, Year order by Year

Charles Kangai, MCDBA, MCT

Books Online is wrong on this. It seems you need to sort on the Set Key
column (column with row headers), and not on the pivot column (as BOL says).
You need to apply the Sort transformation before the Pivot so that it knows
you have sorted.

Charles Kangai, MCT, MCDBA



"Charles Kangai" wrote: