Remembering Y2k

I just stumbled upon an old email I sent out to my fellow geeks on 2/25/99. The industry was full-bore into Y2k refactoring of all systems to prevent potentially cataclysmic consequences (purportedly anyways). That’s all any of us talked about. The email was in regards to a conversation I had late at night with my wife MaryAnn. Here are the details from that evening’s events:



Late last night (Feb 25, 1999) I was working on my home PC with my wife MaryAnn sitting on the bed behind me. I was pretty involved with what I was doing because of the importance of the software that I was installing. MaryAnn was making out a grocery list, asking me the occasional question about it as I worked. Here's the dialogue:

MaryAnn: "Oh, by the way, do we still want a humidifier? When I was at Wal-Mart the other day, I saw a humidifier for $19.99."

Boyd: "A humidifier for 1999???? Since when did anyone care what year a humidifier was supposed to be used in??? What the heck is it.....Y2K compliant or something???? What, are we gonna have a world-wide crash of all pre-Y2k model humidifiers??? That’s the stupidest thing I’ve ever heard of!"

Needless to say, MaryAnn looked at me with a "did you check your brain at the door?" look on her face. When I finally comprehended that she meant $19.99 and not the year 1999, we both had a good crack up for a good 10 minutes or so. I was crying.

I guess it's more obvious with some of us than with others that we've been spending way too much time slinging code.

Pivot Tables in TSQL: A Rant

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:

image

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:

image

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:

image

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 Hot smile)

image

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
  1. Select Data1, Data2, Data3, Data-n
  2. 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
  1. Create Proc [dbo].[upGetComponentSelectionStringForPivot]
  2.     @DivisionKey int,
  3.     @Components Varchar(1000) Out
  4. AS
  5. BEGIN
  6.  
  7.     SET NOCOUNT ON;
  8.  
  9.     Declare @Com Varchar(1000)
  10.     Select @Com = ''
  11.     Select @Com = @Com + '[' + c.Description + '], '
  12.     From Components c
  13.         Inner Join CostCodeComponentXRef x On c.ComponentKey = x.ComponentKey
  14.     Where x.DivisionKey = @DivisionKey
  15.     Group By c.Description
  16.     Set @Components = SUBSTRING(@Com, 0, Len(@Com))
  17.  
  18. END
  19. 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
  1. Create Proc [dbo].[upGetCostCodeComponentPivot]
  2.     @DivisionKey int
  3. AS
  4. BEGIN
  5.     SET NOCOUNT ON;
  6.  
  7.     -- First, create the column headers we'll be building the pivot on.
  8.     Declare @Components Varchar(500)
  9.     Exec upGetComponentSelectionStringForPivot @DivisionKey, @Components Out
  10.    
  11.     -- If we have no columns to select from, then bail.
  12.     If Len(LTRIM(RTrim(@Components))) = 0
  13.          Return -1;
  14.  
  15.     -- Next utilize the pivot column string in the overall query.
  16.     Declare @Query Varchar(2000)
  17.     Set @Query =
  18.         'Select [Cost Code Description], ' + @Components + '
  19.         From
  20.         (
  21.             Select cc.Description [Cost Code Description], c.Description [Component Description], x.PercentComplete [Percent Complete]
  22.             From CostCodes cc
  23.                 Inner Join CostCodeComponentXRef x On cc.CostCodeKey = x.CostCodeKey
  24.                Inner Join Components c On x.ComponentKey = c.ComponentKey
  25.             Where x.DivisionKey = ' + Cast(@DivisionKey As Varchar(25)) + '
  26.         ) As Source
  27.         Pivot
  28.         (
  29.             Min([Percent Complete])
  30.             For [Component Description] In (' + @Components + ')
  31.         ) As ThePivot'
  32.        
  33.     Exec(@Query)
  34.  
  35. END
  36. 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.

Unlocker – A Great Utility for Dealing with Stubborn Files

Yes, it’s been quite a while since blogging. I’ve been doing my hair. Smile

I have an old archive drive that has some old code libraries and db’s I need to resurrect. When I went to copy the SQL Server database files over (*.mdf & *.ldf), I surprisingly received error messages telling me I don’t have “permissions” to the file.

Um, f’real? I mean, once in a blue moon I’ll get a lame message like that from Windows. But this time, it was very unusual. A database I’d built by hand and used on insanely large enterprise levels? Come on.

At this point I’ve no clue why the negative temperament. However, I didn’t really care. I needed the files and needed them quick. So the first thing one does is check out what processes have a hold on my files. A quick search via Process Explorer didn’t turn up anything. So, I then go to the security settings of the files and have some interesting text to read I’ve never seen before in all my travels:

"You do not have permission to view or edit this object's permission settings.”

Now things *really* don’t make sense. I’m quite obviously the proverbial god on my machine. There’s no higher authority (other than Winderz itself). I can neither view the file’s security settings nor take ownership of the files.

Enter Unlocker. A slick little, humble utility that totally P’wned whatever had an invisible hold on the files. By humble I mean, it just does its job and goes home. That’s it.

Upon startup, it’s no-nonsense interface just pulls up a file selection dialog:

image

At this point, the creator, Cedrick Collomb, assumes that if you’ve taken the time to find his utility, download and install it, that you know what you need to do next. So I select my first file (one of four having problems), and get the next dialog:

image

I love it. “However Unlocker can help you deal with this object.” A totally confident statement that Percy on Nikita would make to his Cleaner. The following scene, there’s dead bodies everywhere. I’m going to try to use that moniker in some code soon.

Since I want these files, I select “Move”. It again brings up the selection dialog, but this time it’s looking for a destination folder. After selecting where I want the file moved to, within two seconds, I get the final dialog:

image

Click OK and Unlocker is gone. Job done.

Widgets just don’t get much better than this. Simple. Discrete. Does its job and gets outta dodge.

Great job Cedrick.

Skype Rawks!

I’m using Skype for the first time per a new client requirement. I just got off my first call. I must say the quality was incredible. I’m very impressed.

 

I guess we’ll all have to tune in to see how things go now that Microsoft owns them. The next year or so may very well be interesting.