It’s not earth-shattering, but sometimes “obvious” just smacks you in the nose.

I was working on a project for a customer who is grappling with business models that span large numbers of Excel files, looking for ways to pull the logic out of the gazillions of formulas. At this point, my knowledge of Excel was fairly rudimentary. As a developer, I am focussed on the big tools: IDEs, database management tools, etc.. For me, Excel was just a documentation tool like Word.

I had reluctantly used Excel PivotTables over the years when I needed to pull some statistics out of a sheet full of data. It was a struggle; there were so many controls. Now I needed to model them in SQL.

I looked at the Pivot Table Fields and scratched my head. Then it hit me: A Pivot Table in Excel is nothing more than an old-fashioned Grouping construct in SQL. That’s it. The ‘Values’ are just the aggregates; the ‘Rows’ are just the Grouping terms and the ‘Filters’ is a simple Where clause set. With that insight, it all made sense. It also made my mapping process trivial.

It is a little humbling sometimes to realize what you don’t know. It also pretty exhilarating to make the connection. Just the same, I wish someone had mentioned that fact somewhere along the way.

Related Post

Leave a Comments