Pivoting Rows to Columns - Part 1

Shortly after learning how to query the tables of a database, the first major hurdle many people find is in taking rows of results and "pivoting" them into columns. In the Shelby v.5 world, some of the most common results that people want this way include reporting one column per:

• phone number type
• custom field value
• Profile code

In this post and the next two, I will show three different ways to pivot rows into columns. Each of the three methods has its strengths and weaknesses, so understanding each technique will help you pick the best one for the query you are writing at the time.

The first method is joining to the same table multiple times, once for each separate column you want. This is very similar to the method of joining to the same table more than once that I described in the earlier post on combining husband and wife together on the same row of results. The only difference here is that, instead of just one additional join, there will need to be as many joins as there are columns of data types.

As a starting point of explanation, let me share with you a query that returns a list of names and phone numbers. This list has one row for each person, and an additional row for every phone type after the first one. If the person has three phone numbers in the database, the person will be listed three times.

select

Names.NameCounter,

Names.FirstMiddle,

Names.LastName,

Phones.PhoneNu

from

Shelby.NANames as Names left join

Shelby.NAPhones as Phones on Names.NameCounter = Phones.NameCounter

This would yield the following snippet of results:

Notice how many people are listed multiple times.

In Transact-SQL, the flavor of SQL that runs on SQL Server, we can add an additional restriction onto the join condition between NANames and NAPhones. This additional condition can "pre-filter" the NAPhones table so that it just returns, say, the Main/Home phone type. With such a filtering condition in place, we can once again get just one row per name, since no one has more than on Main/Home phone. I will do this in the query by limiting the NAPhones table to only the rows with a PhoneCounter of 1, which I know represents the Main/Home phone type. Here is the query:

select

Names.NameCounter,

Names.FirstMiddle,

Names.LastName,

Phones.PhoneNu

from

Shelby.NANames as Names left join

Shelby.NAPhones as Phones on Names.NameCounter = Phones.NameCounter and Phones.PhoneCounter = 1

and here are the results:

Now that I can control exactly which phone type is returned by the NAPhones table in any given join to it, I can simply add more joins and filter each one to a specific type of phone number. In the next query I get both the Main/Home phone number and the Business phone number, which always has a PhoneCounter value of 2:

select

Names.NameCounter,

Names.FirstMiddle,

Names.LastName,

MainPhones.PhoneNu as MainPhone,

BusinessPhones.PhoneNu as BusinessPhone

from

Shelby.NANames as Names left join

Shelby.NAPhones as MainPhones on Names.NameCounter = MainPhones.NameCounter and MainPhones.PhoneCounter = 1 left join

Shelby.NAPhones as BusinessPhones on Names.NameCounter = BusinessPhones.NameCounter and BusinessPhones.PhoneCounter = 2

And here are the results:

Notice in the query that I changed the table alias to reflect the type of phone number I was after, and I added aliases to the phone number columns to also reflect the type of number.

At this point, it is a simple matter of repeating the join for any number of phone types, always being sure to change the table alias and the PhoneCounter value to match the phone type. By the way, if you want to know which PhoneCounter values you have in your database, run the following query to find out:

select * from Shelby.NAPhoneTypes

These examples are all about phone numbers, but this same principle can apply to NAProfiles, MBTextPicks, SGMstOrg, and any other table that has a one-to-many relationship with NANames.

Stay tuned for other ways to take a set of rows and pivot them into columns.