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.