BullRunner, following the Market

Configuring the Pivot Component: "Configuring the Pivot Component

By ashvinis
Published: 10/26/2005
Reader Level: Intermediate
Rated: This article has not yet been rated.
Be the first to rate it! Tell a Friend
Rate this Article
Printable Version
Discuss in the Forums


Sometimes coincidences happen that make you sit back and say Hmm. Couple of days back Ovidiu, one of our senior developers, came by and asked me to blog about the Pivot transform. Then earlier today someone doing a high end POC down south need the same information. Hmm.
Pivot transform converts rows into columns. One of the items we will likely not be able to attend to for SQL Server 2005 is a custom user interface for this transform. Thanks to the advanced UI this component is still configurable but that exposes the guts of the transform and perhaps more than you wanted to know. The steps are not pretty but it's better to have such information released than have folks not use features because they don't know how to set it up.
Until BOL gets more in depth information on this topic, here're the steps to configure the Pivot transform. Thanks to Wenyang, one of my colleagues for sending these over... what a team... someone suggests what to write about, someone else writes it up, and I get the limelight.
The Pivot transform converts data that is organized like this:
Table 1:
CustomerProductQty.
SamHam2
SamMilk1
SamBeer6
JoeMilk2
JoeBread1
SallyCheese16

Into similar data that is organized like this:
Table 2:
CustomerHamQtyMilkQtyBeerQtyBreadQtyCheeseQty
Sam21600
Joe02010
Sally000016

Steps:
1. Assume that the data flow contains a path with the three columns and shape of data that is show "