Written by 
My memory is terrible, even though I’ve coded using countless languages over the years, I’m always forgetting things. To be honest though, for many years now I purposefully don’t make an effort to remember the minutiae of most of these languages. Why? Well, I try to keep my focus on figuring out the logic that I need to complete a task rather than what order parameters come in some function. I’d argue that the logic is always the most difficult part of coding, the actual programming itself is relatively straightforward. Why remember the order of parameters when autocomplete or a quick search can remind you. It’s much more important to conceptually know what you need to do in order to complete a task.
With that in mind, I give you one of my personal favourites when writing queries or procedures using T-SQL, the infamous PIVOT and UNPIVOT. I’m always forgetting which translates rows into columns or vice versa, even after years of using them. For reference, PIVOT takes rows and turns them into columns, UNPIVOT does the opposite, so takes columns in a row and turns them into rows. However, in this post we’re going to look at unpivoting columns into rows.
Finally getting to the meat of this post, sorry… there is one issue with UNPIVOT and that is that it eliminates NULL values! So we won’t get a resulting row if the column in the source row is NULL, what if that’s the behaviour we want or need? How do we get around this? Well…
For a start lets first check the results of using UNPIVOT to see what we get and see if its removing NULL values. Hopefully my column naming makes sense…
-- Lets make up some data with columns that we'll want to unpivot into rows
WITH [cteData] AS (
SELECT [RecordID] = 1, [PersonCode1] = 'AAA', [PersonCode2] = 'BBB', [PersonCode3] = 'CCC' UNION
SELECT [RecordID] = 2, [PersonCode1] = 'AAA', [PersonCode2] = 'DDD', [PersonCode3] = 'CCC' UNION
SELECT [RecordID] = 3, [PersonCode1] = 'EEE', [PersonCode2] = 'BBB', [PersonCode3] = 'CCC' UNION
SELECT [RecordID] = 4, [PersonCode1] = 'AAA', [PersonCode2] = 'CCC', [PersonCode3] = NULL UNION -- Note the NULL
SELECT [RecordID] = 5, [PersonCode1] = 'AAA', [PersonCode2] = 'BBB', [PersonCode3] = NULL UNION -- Note the NULL
SELECT [RecordID] = 6, [PersonCode1] = 'CCC', [PersonCode2] = 'EEE', [PersonCode3] = 'FFF' UNION
SELECT [RecordID] = 7, [PersonCode1] = 'AAA', [PersonCode2] = 'DDD', [PersonCode3] = NULL -- Note the NULL
)
-- Select our new rows, where each 'PersonCode' field is now a single column but
-- the values of the original fields are now in several rows ;-)
SELECT
[unpvt].[NewColumnNameForTheValuesHere]
,[unpvt].[ThisIsTheOriginalColumnName]
,[OriginalRecordID] = [unpvt].[RecordID] -- This is the [RecordID] column value from the original row
FROM
[cteData] [data]
-- Lets take the columns in each row and UNPIVOT them into
-- rows with a new column name ;-)
UNPIVOT (
[NewColumnNameForTheValuesHere] FOR [ThisIsTheOriginalColumnName] IN (
[PersonCode1]
,[PersonCode2]
,[PersonCode3]
)
) [unpvt]
Running this code above you’d expect 21 rows (the 7 original rows, times the 3 different fields – which equals 21 rows), but it actually gives us 18 rows!?! The problem is that for ‘RecordID‘ values 4, 5 and 7 we only get 2 rows returned? What gives? Well, these are the rows where one of the ‘PersonCode‘ columns has a NULL value. Unfortunately this is not a problem we can solve with ISNULL, unless we wrapped each of the columns in the source data/table with an ISNULL, but that means we have to provide an alternate value for NULL, this is getting messy…
CROSS APPLY to the rescue! Generally the APPLY operator is used when you want to join a table valued function with an existing table as you cannot do that using normal JOIN syntax. However, it also has some other uses. We can re-write the previous query like this…
-- Lets make up some data with columns that we'll want to unpivot into rows
WITH [cteData] AS (
SELECT [RecordID] = 1, [PersonCode1] = 'AAA', [PersonCode2] = 'BBB', [PersonCode3] = 'CCC' UNION
SELECT [RecordID] = 2, [PersonCode1] = 'AAA', [PersonCode2] = 'DDD', [PersonCode3] = 'CCC' UNION
SELECT [RecordID] = 3, [PersonCode1] = 'EEE', [PersonCode2] = 'BBB', [PersonCode3] = 'CCC' UNION
SELECT [RecordID] = 4, [PersonCode1] = 'AAA', [PersonCode2] = 'CCC', [PersonCode3] = NULL UNION
SELECT [RecordID] = 5, [PersonCode1] = 'AAA', [PersonCode2] = 'BBB', [PersonCode3] = NULL UNION
SELECT [RecordID] = 6, [PersonCode1] = 'CCC', [PersonCode2] = 'EEE', [PersonCode3] = 'FFF' UNION
SELECT [RecordID] = 7, [PersonCode1] = 'AAA', [PersonCode2] = 'DDD', [PersonCode3] = NULL
)
SELECT
[unpvt].[PersonNumber]
,[unpvt].[PersonCode]
,[OriginalRecordID] = [data].[RecordID] -- This is the [RecordID] from the original row
FROM
[cteData] [data]
-- We can use CROSS APPLY to unpivot the data from columns into rows whilst keeping NULL values! If
-- we use UNPIVOT command the NULL's are eliminated. So you can choose which method depending
-- on if you need to preserve NULL values or not ;-)
CROSS APPLY (
VALUES
(1, [data].[PersonCode1])
,(2, [data].[PersonCode2])
,(3, [data].[PersonCode3])
) [unpvt] (
[PersonNumber] -- New column for the first parameter in the VALUES above
,[PersonCode] -- New column for the second parameter in the VALUES above
)
Note that we’re using the VALUES (table value constructor) to build the result set, then we specify the new column names of the result set (in our example, ‘PersonNumber‘ and ‘PersonCode‘). This give us the full 21 rows we were expecting.
Anyway, hope that comes in useful, its something I’ve needed a number of times for specific reasons. To be honest, I actually prefer the syntax of the CROSS APPLY method, but that is of course personal taste. Let me know your thoughts 🙂