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 "

The Escapist - Outsourcing to America: "No, to see the real threat to American industry, we need to look across the Himalayas into China.
China's one billion-strong nation is on the grow, and they've caught the industrialization bug something fierce. As factories sprout up in what were once farming fields and gas prices soar to transport goods across a landmass bigger than the continental U.S., children are being educated with a very Eastern approach to Western production. Think of post-war Japan, only with ten times as many people. The Chinese government is quickly warming up to the idea of tipping the scales of commerce in their direction, and within the next century, it's likely they're going to have a shot at doing it.
And, remarkably, they're taking a stab at gaming.
They're investing $1.8 billion dollarsin online games over the next five years alone. Consider the fact their population is quickly moving into the digital foray; that only means more customers for everybody. Combine that with their recently passed laws to curb online gaming 'addiction.' What do you see?A change in development paradigm; all of a sudden the gaming market is flooded with people forced into casual gaming, and the Chinese government is buying for $1.8 billion worth of games to fit that standard. If American companies can even hope to capture the hearts and minds of the Chinese populace, it means they're going to need to show up at the Chinese Embassy in Washington, D.C., with some very revolutionary ideas on how to make a good online game. And it has to be more than a money grab if they want to remain in the land of a thousand dynasties for any discernable length of time.
But the Chinese aren't going to pay for games developed externally. Why would they? Part of a country's investment in homegrown production is the return will potentially increase their GD"

Canadian Business | The 2005 Investor 500: "CI Fund Management Inc."

The original stock markets evolved from coffee houses where investors would meet and swap paper over coffee. Gradually, as volumes grew, the participants at the coffee tables became agents for others. Eventually, the coffee houses were replaced with full scale trading floors which evolved into the sophisticated web-based trading systems we use today. Well, you can still do it the old way, and I sometimes do. It's a slow, somewhat painful process which instills (in me anyway) a sense of nostalgia and history. Trading shares directly over the table with family and friends can save you a little money (no transaction costs!) and it can be fun.Link