Listing Husband and Wife on One Line - Part 2

Last week I posted on how to put both husband and wife on one line by joining to the names table (NANames for Shelby v5) multiple times. That is a useful technique when you want to show the spouse information for every name that is part of the desired result set. However, a different technique is needed when you want to show spouse information only when both spouses are part of the desired result set. A typical example might be a label report for the choir or another group in which one or both of the spouses might participate, and when both spouses do participate, they should both be combined. When only one spouse participates, the other should not be part of the results.

This new method selects the names as you would normally, joining only once to the names table. But then add a GROUP BY clause to the query, grouping by the common factor for the family (NANames.FamNu for Shelby v5). After you group the results, you can use a CASE statement to select either the Head of House or the Spouse name to appear in the results.

In the example query below I have selected all the adult choir members by using an inner join to the NAProfiles table and at the same time limiting the included Profile values to those that begin with MUAC, which is a common prefix on all the adult choir profile codes. The query uses GROUP BY and CASE statements to pull the spouses together and list them in the appropriate columns of the results.


select
HHFirstName = max(case when Names.UnitNu = 0 then Names.FirstMiddle end),
HHLastName = max(case when Names.UnitNu = 0 then Names.LastName end),
SPFirstName = max(case when Names.UnitNu = 1 then Names.FirstMiddle end),
SPLastName = max(case when Names.UnitNu = 1 then Names.LastName end)
from
Shelby.NANames as Names inner join
Shelby.NAProfiles as Profiles on Names.NameCounter = Profiles.NameCounter and Profiles.Profile like 'MUAC%'
group by
Names.FamNu


Here are the results:

You can see that the Blackwells , the Carters, and the Glovers are in the choir together. In the visible results all the other individuals do not have a spouse in the choir.

With the two techniques from last week and this week, you should be able to get the names of spouses together whenever you need them in one row of results. Of course, listing them on one row is usually only part of the requirement. Often you will want to combine them together into one column as well, with a result such as "John & Jane Doe" or "Mr. & Mrs. Smith." That process is surprisingly complex, and will be the topic for next week's post.