"Charles Kangai" wrote:

> 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
>