One of my biggest complaints in the industry to which I currently belong is the arrogance, ego and on occasion downright foolishness I see quite often when it comes to educating others on understanding what should be simple concepts (after a bit of explanation that is). I don’t mean that to be a blanket statement. Those attitudes are surely not prevalent everywhere. I know some incredibly intelligent folks who are very, very good at teaching others. But those negative characteristics are quite prevalent nevertheless. Here’s a perfect, textbook example of what I’m talking about. Pretend your resident n00b programmer asks someone experienced what an interface is. Here’s the status-quo answer given:
“An Interface is a Contract”
Huh? Wha? What the heck does that mean? And yet that’s what I’ve seen in many a book. It’s the answer provided me by interviewees in many an interview I’ve given (most times with no further explanation on usefulness). And it’s also the response during many a conversation with someone who knows what an interface is. But too often I’ve seen those new to programming shake their heads up and down eagerly indicating they understand that statement completely. But in reality, they don’t. They’re clueless. Their code shows it quite clearly. And only when I pull them aside and get them to confide in me does the Truth come out: They’re freaking clueless. And rightfully so. I’ve counseled many people over the years on what an interface is and the virtues of them. So many never understood them until I explained. When I explain in non-geek-textbook-speak, the light goes on and they’re finally reconciled on the beauty of interfaces.
But this isn’t about interfaces. We’re wanting to talk about Pivot Tables today.
What the Experts Say about Pivot Tables
Here’s what they say:
“A Pivot Table is a transformation of flat data into something more clear.”
Um, yea. Clear. Right. Speaking of clear, that explanation is clear as mud. Even Wikipedia’s description doesn’t help the n00b who’s desperately looking for answers (though a person could probably pull the answer out of their example). That is if that n00b even knew that they need to find out what a pivot table is. The closest they come to answering the question is the word “rotation” used in the definition. Good effort, but still not there. The closest I’ve seen out there to the Truth is the WhatIs (TechTarget) definition. But still, not quite very good.
And don’t get my started on the nonsense that exists in the blogs I’ve seen. Though I’ve surely not scoured the entire net, it’s the same nonsense copy & pasted from textbooks.
What is a Pivot Table?
So, here’s a lesson in Pivot Tables. Hopefully it’ll also be a lesson on how to properly teach people on a subject. To start, here’s my own personal definition. This is for those who’ve no clue what a pivot table is:
“A Pivot Table is a table in which row data from one table has been “rotated” or “pivoted” into the place of column related data in another table, or vice-versa.”
A Solid Example of a Pivot Table
That may not quite make sense just yet. So let’s take it a step further: What would a definition be without examples? Here’s a table with some data. The data has not been pivoted yet:
Table 1
Notice the redundant data. We’re pulling all permutations of Cost Code to Component percentages. Now imagine a huge list of Cost Codes with an even larger list of Components! That would be one long and butt-ugly report, wouldn’t it?
A Pivot Table takes the data of an original resultset (found in the Rows), yanks it out and turns that data into categories of data by turning that data into Columns:
Diagram 1
Notice in the diagram we’re taking the data categorized underneath “DataColumn”, sliding it all out of the table and rotating it to instead become column headers. This is the “pivot” in “pivot tables”.
So now let’s use real data. Taking a look at the data in Table 1 above, it seems that we’d want to aggregate (group, add up) the percentage data that’s common to Components and show them by Cost Codes. For those who are new, here’s the key to your understanding: We’re going to pull the “Component Description” column out and pivot that data into “columnar” data instead of row data.
So, let’s first take a look at the finished pivot table, then we’ll go into the explanation:
Table 2
Do you see what we’ve done? We’ve taken what was the return result (the data returned from the previous query on the table above) and we’ve changed that resulting percentage data to be based on Cost Code. A much better report that’s must easer on the eyes and brain that the previous. We’ve “rotated” or “pivoted” what was vertical data in the table to now being horizontal data. In doing so, we were able to effectively aggregate (group) the redundant Cost Code data. “How cool is that?” (Mark Aitkenhead, circa 2012 
)
Diagram 2
The Code
Now let’s take a look at the SQL.
The standard example of using pivot tables in TSQL typically shows pre-knowledge of the column data that one would want to use. Take another look at Diagram 1 above. In other words, most examples assume you know ahead of time all the data that’ll show up underneath “DataColumn” and would therefore be able to hardcode it into a SQL statement to create your pivot table:
Hard-Coded Pivot Selections
- Select Data1, Data2, Data3, Data-n
- From...
Snip 1
Being severely allergic to hard-coding, I refuse to do so. You should also refuse. Some of the most common famous last words in programming are, “It’ll never change.” That statement is the sign of either a n00b or a fool. Avoid hard coding and magic numbers/values at all costs.
Here’s the TSQL solution for pivoting your data without knowing ahead of time the number or names of the columns that’ll be pivoted. In our examples above, that would be the “Component Description” data. First, we’re going to create a sproc that dynamically builds the list of Component Description columns we’ll be selecting:
Column String Builder
- Create Proc [dbo].[upGetComponentSelectionStringForPivot]
- @DivisionKey int,
- @Components Varchar(1000) Out
- AS
- BEGIN
-
- SET NOCOUNT ON;
-
- Declare @Com Varchar(1000)
- Select @Com = ''
- Select @Com = @Com + '[' + c.Description + '], '
- From Components c
- Inner Join CostCodeComponentXRef x On c.ComponentKey = x.ComponentKey
- Where x.DivisionKey = @DivisionKey
- Group By c.Description
- Set @Components = SUBSTRING(@Com, 0, Len(@Com))
-
- END
- Go
Snip 2
Of course, your overall SQL structure will surely look differently. This SQL is based on a live project. But you should get the gist of what we’re attempting to accomplish here. We’re building the column list that’ll be used in a Select statement in another sproc. And now here is the final SQL that’ll render us our data pivoted:
Pivot Sproc
- Create Proc [dbo].[upGetCostCodeComponentPivot]
- @DivisionKey int
- AS
- BEGIN
- SET NOCOUNT ON;
-
- -- First, create the column headers we'll be building the pivot on.
- Declare @Components Varchar(500)
- Exec upGetComponentSelectionStringForPivot @DivisionKey, @Components Out
-
- -- If we have no columns to select from, then bail.
- If Len(LTRIM(RTrim(@Components))) = 0
- Return -1;
-
- -- Next utilize the pivot column string in the overall query.
- Declare @Query Varchar(2000)
- Set @Query =
- 'Select [Cost Code Description], ' + @Components + '
- From
- (
- Select cc.Description [Cost Code Description], c.Description [Component Description], x.PercentComplete [Percent Complete]
- From CostCodes cc
- Inner Join CostCodeComponentXRef x On cc.CostCodeKey = x.CostCodeKey
- Inner Join Components c On x.ComponentKey = c.ComponentKey
- Where x.DivisionKey = ' + Cast(@DivisionKey As Varchar(25)) + '
- ) As Source
- Pivot
- (
- Min([Percent Complete])
- For [Component Description] In (' + @Components + ')
- ) As ThePivot'
-
- Exec(@Query)
-
- END
- Go
Snip 3
Again, you’ll need to tweak the SQL (within the red text) to your specific selection needs. But overall, the “Pivot” keyword, coupled with the appropriate selection criteria will rotate your data accordingly.
Take your time in looking at the code. And be sure to comment and let me know if you need any help or have any questions.
