Error using pivot transform

Hey, did someone try to use the pivot transform?
It's not what you would call easy to configure...

In BOL there is the following section:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/55f5db6e-6777-435f-8a06-b68c129f8437.htm

I have followed the instructions there and I get an error when I try to map the new output column to the pivotkey:

"Output column "Yellow" (69) cannot be mapped to PivotKey input column"

So I mapped the new output column to the column holding the quantity values. This worked.
May this be a bug in BOL?

Further on I got some strange behaviour of the pivot transform:

My input is the following CSV-Flatfile:
sk1;sk2;pk1;qty
A;1;Yellow;2
A;1;Green;1
B;1;Yellow;5
A;2;Blue;3
A;2;Green;9
A;1;Green;5

If I omit the last line, everything is fine.
When passing the file as is into the pivot transform the output is:

SK1 SK2 Yellow Green Blue
A 1 2 1 NULL
B 1 5 NULL NULL
A 2 NULL 9 3
A 1 NULL 5 NULL

As you can see the key a;1 is duplicate. The cols SK1 and SK2 are my primary key of the destination table.

When the input is sorted by the first two colums the pivot transform throws an Error:

Error: 0xC02020CF at Pivot w sort, Pivot [39]: Duplicate pivot key value "Green".

I expected to have a sum over all Green for the key A;1. It seems that I have to use an aggregate which in my opinion should
be obsolete here.

As a conclusion I have to say that this task is far away from perfect but not bad for a start.

Regards
Fridtjof

>>May this be a bug in BOL?

Yes, it was. This was reported by Jamie as well and I believe this had been fixed in BOL web release back in last fall.

What you noticed in by design, Pivot transform requires its input rows to be sorted on SetKey columns, (this should be on BOL as well, I will confirm later), and there should be no duplicate rows on the same SetKey on a same pivot key value - in other words, if you have

A, 1 Green 1

A, 1 Geen 5

Remember, after Pivoting, Green will become a column, and these two rows will cause conflicts on pivot's output row for the same "A,1" on that "Green" column.

Yes, you will need a Aggregate, Pivot does not do any aggregation.

HTH

Wenyang

|||Thanks Wenyang,

by the way: The mapping method in this task should be worked on to make it easier for noobs like me to do the mapping :)

Another point: If the transform requires to be sorted on the SetKey then why doesn't it claim for it to be sorted? Ok that may correspond to the issue above...

HAND
Fridtjof