Problem
I often see people struggle with poorly normalized schemas, where they have to transpose a query against a wide table into skinnier, more normalized output.
For example, imagine a table with data like this:
For a certain report, the desired output is actually as follows (apologies for the messy arrows):
Ideally, you would just fix the schema, but I do acknowledge that this is not a realistic possibility in a lot of scenarios.
Solution
So, we need to code our way around this to present the data as it should be stored, in spite of how it is being stored. Here is some sample data:
One way that might seem like a good idea is to perform a UNION:
The problem with this solution is that it does not scale - for every phone column you have, you add an additional scan, and you also have to add an entirely new UNION ALL query for every phone column.
Instead, we can use SQL Server's UNPIVOT operator. Which, contrary to popular belief, is not quite the opposite of PIVOT. An example using the above sample data:
Results:
Now, I'll admit, the syntax is not as intuitive as you might expect. Most notably, that "Phones" alias is completely made up, and you could put anything there (except "Phone"). Basically what it is saying is, "extract a new row for every value you find in the columns Phone1, Phone2, and Phone3."
Another case
Another situation you might find is when you have multiple sets of related columns. So, for example, let's say you have a column for the type of each phone number:
Now, we could also solve this with a UNION query, but let's take a look at another capability we have: using multiple UNPIVOTs. The only complication here is matching the output phone to the corresponding phone type - for this we need to do some string interrogation to ensure that Phone1 matches to PhoneType1, Phone2 matches to PhoneType2, etc.
Results:
In my next tip, I'll show how to derive these UNPIVOT queries dynamically, without having advanced knowledge of how many Phone/PhoneType columns there will be. Since a weakness of this particular design pattern is that when a 4th phone is added, you add new columns and change all the queries, and when a 5th phone is added, you add new columns and change all the queries, a more dynamic query can be useful to minimize or eliminate queries of this type.
Source Collected from MSSQLTIPS.COM
No comments :
Post a Comment