SQL Puzzle Series 2: Rearranging contact information

In case you have ever filled out a registration form, you would have been asked to provide a home, cellular and work contact. How is this information efficiently stored?

Photo by Luca Bravo on Unsplash

One can only imagine a table with one person’s name and his contact information available horizontally. You would be surprised to find this information would most likely stored vertically with your name/customer ID appearing multiple times and the type of contact information with the final phone number.

The data above is present in a table called ContactInfo, and we are going to try a few different ways to present this information in a readable fashion. Intuitively the best way to view this information would be to have CustomerID as a primary key with just one entry and the different types of contact information present horizontally, as shown below.

NULLs indicate that information was not provided.

This can be down in many different ways.

1. Using CASE WHEN along with MAX:

With this technique we can use the CASE WHEN statement to check if the type of number is either Personal, Mobile or Emergency.

But as you can see the problem of duplicated entries for CustomerID is not solved and also you can see that every entry has a NULL in case the condition is not satisfied. To solve this problem we can use the MAX function and GROUP BY the CustomerID.

Now we have the output we were looking for.

2. Using CTEs and JOINs:

This technique involves using CTEs for each of the 3 categories and then joining on the CustomerIDs to finally get the expected result.

Basically creating smaller views from the main data

Now that we have 3 views — Emergency, Mobile, and Personal, you can join this with the CustomerID from the ContactInfo table.

Now we have the output we were looking for.

3. Inbuilt PIVOT-UNPIVOT feature:

For the last way to solve this we can use the PIVOT-UNPIVOT function. For documentation please refer to this very helpful document — Using PIVOT and UNPIVOT — SQL Server | Microsoft Learn

The problem with this method is that not all versions of SQL will have this PIVOT-UNPIVOT function. For example, MySQL does not support this feature, which where you would resort to the above-mentioned methods.

The code can be found here.

References:

1. Joe Celko’s Advanced SQL Programming
2. Advance SQL Puzzles