CROSS APPLY as an alternative to UNPIVOT

This morning, I was listening to the inimitable SQL Server Radio podcast, with Matan Yungman and Guy Glantser. They mentioned how Itzik Ben-Gan had documented a great way to turn CROSS APPLY into an extensible UNPIVOT command. I was intrigued, since the CROSS APPLY can be used for so many wierd and wonderful things, and decided to check it out.

I looked at Itzik’s excellent post about the subject over at SQL Server Magazine, and decided to do a blog post of my own, with entirely self-contained code.

CROSS APPLY shares some aspects of CROSS JOIN. A cross join simply combines all the rows from the table on the left of the cross join with all the rows in the table on the right of the cross join. An example of how CROSS JOIN works:

The output from the SELECT statement above:

+-----+-----+
| num | num |
+-----+-----+
|   4 |   7 |
|   4 |   8 |
|   4 |   9 |
|   5 |   7 |
|   5 |   8 |
|   5 |   9 |
|   6 |   7 |
|   6 |   8 |
|   6 |   9 |
+-----+-----+

As you can see, SQL Server outputs all the rows in #Num2 for each row in #Num1, essentially multiplying the count of rows in each table; since each table has 3 rows, we get 9 (3×3) rows in the output. Extending this example a bit more, lets perform some actions on the columns:

The output:

+-----------+-----------+----+----+
| #Num1.num | #Num2.num | x  | +  |
+-----------+-----------+----+----+
|         4 |         7 | 28 | 11 |
|         5 |         7 | 35 | 12 |
|         6 |         7 | 42 | 13 |
|         4 |         8 | 32 | 12 |
|         5 |         8 | 40 | 13 |
|         6 |         8 | 48 | 14 |
|         4 |         9 | 36 | 13 |
|         5 |         9 | 45 | 14 |
|         6 |         9 | 54 | 15 |
+-----------+-----------+----+----+

Now, the common question here would be how to unpivot the “x” and “+” columns. Since CROSS APPLY works similarly to CROSS JOIN in that it applies values from the right side of the CROSS APPLY against each row on the left side of the CROSS APPLY, we can take advantage of the “multiplication effect” of the cross join to generate multiple rows for each row in the above output:

The results:

+-----------+--------+-----------+---+--------+
| #Num1.num | Action | #Num2.num |   | Result |
+-----------+--------+-----------+---+--------+
|         4 | +      |         7 | = |     11 |
|         4 | x      |         7 | = |     28 |
|         4 | +      |         8 | = |     12 |
|         4 | x      |         8 | = |     32 |
|         4 | +      |         9 | = |     13 |
|         4 | x      |         9 | = |     36 |
|         5 | +      |         7 | = |     12 |
|         5 | x      |         7 | = |     35 |
|         5 | +      |         8 | = |     13 |
|         5 | x      |         8 | = |     40 |
|         5 | +      |         9 | = |     14 |
|         5 | x      |         9 | = |     45 |
|         6 | +      |         7 | = |     13 |
|         6 | x      |         7 | = |     42 |
|         6 | +      |         8 | = |     14 |
|         6 | x      |         8 | = |     48 |
|         6 | +      |         9 | = |     15 |
|         6 | x      |         9 | = |     54 |
+-----------+--------+-----------+---+--------+

The code for a more complicated example that performs calculations on the unpivoted columns:

The output looks like:

cross-apply-unpivot-data

As you can see in the output, we’ve turned the 4 columns, DriveCSize, DriveCFree, DriveDSize, and DriveCFree into 4 rows, and included columns for the total, and a percentage of the total taken by each value.

For cases where you’re dealing with a smallish number of “pivoted” columns, you can re-write the CROSS APPLY to eliminate the CASE statement, and simplify the overall query. For instance,

The idea here is to use the CROSS APPLY to do the work of the CASE statement by using it to pull the data for the other drives’ “size” or “free” column, to use as the basis for the total and percent of total columns in the final output.