Pivoting Rows to Columns - Part 3

In this last installment of this series on pivoting rows into columns, I will show you how to use GROUP BY and CASE together to achieve the desired result. The specific example will take Profile codes from the NAProfiles table of the Shelby v.5 database and pivot them into columns.

Here is the basic query without pivoting, showing each name in the database along with Profile codes:

select

Names.NameCounter,

Names.FirstMiddle,

Names.LastName,

Profiles.Profile

from

Shelby.NANames as Names left join

Shelby.NAProfiles as Profiles on Names.NameCounter = Profiles.NameCounter

This generates a list of names and Profile codes, but a person with three codes will be listed three times. What many people want is to list the person once, with a column for each Profile code of interest. The first step in that direction for the approach today is to add a GROUP BY clause and an aggregate function for the Profile code:

select

Names.NameCounter,

Names.FirstMiddle,

Names.LastName,

Profile = max(Profiles.Profile)

from

Shelby.NANames as Names left join

Shelby.NAProfiles as Profiles on Names.NameCounter = Profiles.NameCounter

group by

Names.NameCounter,

Names.FirstMiddle,

Names.LastName

Now we are back to one line per person, but with only one Profile in the results. Because of the MAX() function, we have the Profile that comes last alphabetically for each person. To gain control over which Profile shows up, add a CASE statement that returns only the desired value inside of the MAX() function:

select

Names.NameCounter,

Names.FirstMiddle,

Names.LastName,

Profile = max(case Profiles.Profile when 'LASTCN' then Profiles.Profile end)

from

Shelby.NANames as Names left join

Shelby.NAProfiles as Profiles on Names.NameCounter = Profiles.NameCounter

group by

Names.NameCounter,

Names.FirstMiddle,

Names.LastName

This returns only the LASTCN Profile code in the Profile column of the results. Now that you have a method of getting the info from NAProfiles on just row(s) you want, you can use a variety of NAProfile columns and column aliases to get the desired results. You can even substitute your own marking as the output of the CASE statement, such as printing an "X" to indicate a match.

select

Names.NameCounter,

Names.FirstMiddle,

Names.LastName,

LastCNDate = max(case Profiles.Profile when 'LASTCN' then convert(varchar, Profiles.Start, 101) end),

AllergyInfo = max(case Profiles.Profile when 'ALLERG' then Profiles.Comment end),

OnReachingTeam = max(case Profiles.Profile when 'ZRREACH' then 'X' end)

from

Shelby.NANames as Names left join

Shelby.NAProfiles as Profiles on Names.NameCounter = Profiles.NameCounter

group by

Names.NameCounter,

Names.FirstMiddle,

Names.LastName

This approach works best when you have only one join that creates a one-to-many relationship, such as NANames to NAProfiles. If you have several joins of that type, then one of the first two approaches (discussed in the prior two blog posts) would probably work better. But when you do have just one such join, this is perhaps the easiest way to move rows to columns.

Pivoting Rows to Columns - Part 2

The last post looked at how to pivot rows to columns using multiple joins to the same table. In this post I will explain how to pivot using multiple subqueries in the SELECT clause of the query.

I will also be using a special "view" object in the example solutions. Views are essentially the results of queries that have been saved into the database in such a way that you can use the sets of results just as you would use a table. The view I'm going to use is called Shelby.VIEW_SHELBY_CUSTINFO_ALL. This is a view of the custom information collected on the Custom Tabs of the GlobaFILE module in Shelby v.5. Each row of this view represents one custom value for one name entry in the NANames module.

Here is an example of results from the Shelby.VIEW_SHELBY_CUSTINFO_ALL view:

select * from Shelby.VIEW_SHELBY_CUSTINFO_ALL

Now, let's limit this down to just the Spiritual Gifts tab and the Teacher check box option on that tab:

select * from Shelby.VIEW_SHELBY_CUSTINFO_ALL as CustomInfo where CustomInfo.TabName = 'Spiritual Gifts' and CustomInfo.FieldName = 'Teacher'

So far, so good. Bear with me just a moment as we follow this train of thought two steps further. Let's further limit this down to the value of the Teacher field for just one person. For this example, I will limit it down to the person with a NameCounter value of 8:

select * from Shelby.VIEW_SHELBY_CUSTINFO_ALL as CustomInfo where CustomInfo.TabName = 'Spiritual Gifts' and CustomInfo.FieldName = 'Teacher' and CustomInfo.NameCounter = 8




Now, one last refinement before we talk about how to use this info in a subquery. Let's just get the one column of information we really want out of this view, the actual "true/false" value of the field.

select CustomInfo.Value_c from Shelby.VIEW_SHELBY_CUSTINFO_ALL as CustomInfo where CustomInfo.TabName = 'Spiritual Gifts' and CustomInfo.FieldName = 'Teacher' and CustomInfo.NameCounter = 8

This does not look like much at all, but it is precisely what we need to know about the custom value of the "Teacher" field for one person. (The negative one represents "true," meaning that this person has the check box checked on his or her record.)

Now we can look at how to meld this information as a subquery into a main query that pulls the main name information out of the database.

In case you have not used subqueries in the SELECT clause of a query before, here is a run-down of the basic principles:

• A subquery uses the same syntax as a regular query.
• A subquery can have its own SELECT, FROM, WHERE, GROUP BY, and HAVING clauses, just like a regular query.
• A subquery that is returning a value to a SELECT column must return exactly one value each time it executes: one column and one row only.
• A subquery may be correlated to the primary query by using one or more columns from tables in the primary query as part of conditions in the WHERE clause of the subquery.

What we need to do it correlate the query we wrote above with another query that pull name information out of the database. Note the bold text in the following query, which shows how the correlation is happening:

select

n.NameCounter,

n.FirstMiddle,

n.LastName,

SpiritualGift_Teacher = (select CustomInfo.Value_c from Shelby.VIEW_SHELBY_CUSTINFO_ALL as CustomInfo where

CustomInfo.TabName = 'Spiritual Gifts' and CustomInfo.FieldName = 'Teacher' and CustomInfo.NameCounter = n.NameCounter)

from

Shelby.NANames as n

What is happening is that for each row of results the subquery executes, substituting the value of the NameCounter from NANames on that row for the WHERE condition in the subquery. Once we have one subquery added, it is quite easy to add more to pull additional custom values into the results:

select

n.NameCounter,

n.FirstMiddle,

n.LastName,

SpiritualGift_Teacher = (select CustomInfo.Value_c from Shelby.VIEW_SHELBY_CUSTINFO_ALL as CustomInfo where

CustomInfo.TabName = 'Spiritual Gifts' and CustomInfo.FieldName = 'Teacher' and CustomInfo.NameCounter = n.NameCounter),

SpiritualGift_Encouragement = (select CustomInfo.Value_c from Shelby.VIEW_SHELBY_CUSTINFO_ALL as CustomInfo where

CustomInfo.TabName = 'Spiritual Gifts' and CustomInfo.FieldName = 'Encouragement' and CustomInfo.NameCounter = n.NameCounter),

SpiritualGift_Service = (select CustomInfo.Value_c from Shelby.VIEW_SHELBY_CUSTINFO_ALL as CustomInfo where

CustomInfo.TabName = 'Spiritual Gifts' and CustomInfo.FieldName = 'Service' and CustomInfo.NameCounter = n.NameCounter)

from

Shelby.NANames as n

In case you would like a comparison, here is the phone number example from the previous post done with this subquery technique:

select

n.NameCounter,

n.FirstMiddle,

n.LastName,

MainPhone = (select p.PhoneNu from Shelby.NAPhones as p where p.PhoneCounter = 1 and p.NameCounter = n.NameCounter),

BusinessPhone = (select p.PhoneNu from Shelby.NAPhones as p where p.PhoneCounter = 2 and p.NameCounter = n.NameCounter)

from

Shelby.NANames as n

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.

GLAcct Table Gotcha

Assumptions are the bane of any query project, and one assumption I recently made bit me today. I had assumed that only Income and Expense type accounts would have any value stored for the ClosingAcctNu column in the GLAcct table. After all, only those two types of accounts use a closing account. In the software they are the only kinds of accounts that allow you to see or to enter any value for closing account information.

I was wrong. Somehow the ClosingAcctNu column in a customer's data was populated with account values for header accounts, total line accounts, and possibly others as well. This was not a conversion issue; these were brand new accounts in a brand new chart of accounts. I had to adjust my query for this customer in order to specifically restrict the search for closing accounts to the detail account type, in order to avoid picking up erroneous closing account information from the Header and Total Line rows in the GLAcct table.

This was particularly frustrating because it only became apparent in the "real world" environment; in my test database there are no extraneous closing account values in the GLAcct table.

Whenever you get unexpected results, looking for the culprit can be tricky. Just be prepared for the unexpected to sometimes be lurking even in a query of familiar tables.

Calculate a Future Date

Many reports I create are roll sheets or other date-specific reports that need to print a future date, usually the "next Sunday" after "today." Let's walk through the process of finding the "next Sunday" on the calendar.

First, remember the function to return today's date:

select Today = getdate()

Second, remember that we can move the date into the future by adding a value equal to the number of days we want to move. Thus, the date for tomorrow would be calculated this way:

select Tomorrow = getdate() + 1

Third, it is helpful to keep in mind that each day of the week is assigned a digit from 1 (for Sunday) through 7 (for Saturday). The SQL statement that returns the current day-of-the-week digit value is:

select DayOfTheWeekDigit = datepart(dw, getdate())

Thus, what we need to figure out is "How many days after today will be next Sunday?" Obviously, if we are asking that question on Sunday the answer is seven. If we are asking that question on Monday the answer is six, on Tuesday it is fix, and so on through Saturday when it is one. Applying some basic math concepts, we can eventually derive the following formula:

"Next Sunday" = "Today" + (8 - "Today's Day of the Week Digit")

Substituting the acutal SQL syntax for this formula we have:

select NextSunday = getdate() + (8 - datepart(dw, getdate()))

Simply changing the 8 to a 9 will calculate next Monday, a 10 will calculate next Tuesday, and so on. By adjusting this formula, you can calculate any future day of the week.

Rollup and Grouping Functions

If you have done many queries at all, you have probably used the GROUP BY clause to generate aggregate values across a subset of rows. For instance, here is a simple query to calculate the total amount given to each Purpose Code for each year of history in the Shelby v.5 database:

select
year(hst.CNDate) as GiftYear,
pur.Purpose as GiftPurpose,
sum(det.Amount) as TotalGiving
from
Shelby.CNHst as hst inner join
Shelby.CNHstDet as det on hst.Counter = det.HstCounter inner join
Shelby.CNPur as pur on det.PurCounter = pur.Counter
group by
year(hst.CNDate),

pur.Purpose
order by
year(hst.CNDate),

pur.Purpose

This query yields one row per year/purpose combination, showing the total receipts for each purpose in each year.

This is fine as far as it goes, but a simple addition can also give us subtotals for each year of all purposes and a grand total of all years and purposes. All you have to do is add the key words WITH ROLLUP at the end of the GROUP BY clause.

select
year(hst.CNDate) as GiftYear,
pur.Purpose as GiftPurpose,
sum(det.Amount) as TotalGiving
from
Shelby.CNHst as hst inner join
Shelby.CNHstDet as det on hst.Counter = det.HstCounter inner join
Shelby.CNPur as pur on det.PurCounter = pur.Counter
group by
year(hst.CNDate),

pur.Purpose with rollup
order by
year(hst.CNDate),

pur.Purpose

With that simple addition, the results would look like this:

The NULL values are rather unfortunate, though. It would probably be better to replace them with a descriptive phrase to show that the row is a total line. T-SQL includes a function called GROUPING() that helps with that. The GROUPING() function returns a 1 whenever the column inside the parentheses returns a NULL because it is part of a ROLLUP function. It returns a 0 if it is not NULL or if it is a NULL for some other reason other than a ROLLUP function. Thus we can use GROUPING to test for ROLLUP nulls and translate them into better values. Here is the example with the simple query we have been using.

select
case grouping(year(hst.CNDate)) when 1 then 'All Years' else cast(year(hst.CNDate) as varchar(4)) end as GiftYear,
case grouping(pur.Purpose) when 1 then 'All Purposes' else pur.Purpose end as GiftPurpose,
sum(det.Amount) as TotalGiving
from
Shelby.CNHst as hst inner join
Shelby.CNHstDet as det on hst.Counter = det.HstCounter inner join
Shelby.CNPur as pur on det.PurCounter = pur.Counter
group by
year(hst.CNDate), pur.Purpose with rollup
order by
year(hst.CNDate), pur.Purpose

And here is a sample of the results:

The final step would be to use GROUPING() in conjunction with the ORDER BY clause in order to move the total and subtotals to the bottom of each section, where most people expect to find them. I will leave that exercise for you.

SQL Saturday in Richmond, VA

I know it has been awhile since I posted anything. Thanksgiving, Christmas, and New Year's Day have all preempted my usual posting schedule. Even now I just have time to post a brief message about the upcoming SQL Saturday in Richmond, VA. On Saturday, January 30th, there will be a SQL Saturday event at the ECPI College of Technology. I will be presenting a session on SELECT Query Fundamentals at this event, and there are lots of other topics there too, for beginners all the way up to experts.

If you want to learn more about SQL and if you can be in Richmond on January 30th, SQL Saturday is the place to be.

Hope to see you there!