Introduction
TL;DR : Why to use UNPIVOT
over UNION ALL
? Performance, UNION ALL
scans all the data for each subquery, with UNPIVOT
the scan happens only once.
I have written few technical guides on how to use CROSS JOIN
and UNPIVOT
in different dialects of SQL, you may check here :
However I wish to write even simpler guide using only cell's in a spreadsheet, I have used Google Spreasheets
The motivation behind is pretty trivial : numerous views for the articles and not so many likes, consequently the subject is not easy to understand and to fix it I will present the problem in as simple way as possible.
There will be 0 code in this guide.
As Stephen Hawking said :
Someone told me that each equation I included in the book would halve the sales. I therefore resolved not to have any equations at all. In the end, however, I did put in one equation, Einstein's famous equation, E = mc squared. I hope that this will not scare off half of my potential readers.
This guide is definitely not a masterpiece as A Brief History of Time
hence there will be no equations, no formulae, no code.
Only cells of few colors, I will be using orange and blue. And few shades of gray.
UNPIVOT
Let's start with a simple example.
Hereby I wish to stockpile columns A, C into column A and columns B, D into column B, consequently the mapping is:
A1 -> A1; B1 -> B1; C1 -> A2; D1 -> B2;
and the result :
Now let's add another row into the dataset:
The mapping is the following
A1 -> A1; B1 -> B1; C1 -> A2; D1 -> B2;
A2 -> A3; B2 -> B3; C2 -> A4; D2 -> B4;
Great! Let's see how to visualize the CROSS JOIN
.
CROSS JOIN
CROSS JOIN
is pretty simple, take everything on the left and produce a pair with everything on the right.
Let's color just one cell into orange and one into blue
CROSS JOIN
is just trivial :
AxB -> (A1,B1)
Let's add another cell of each color:
AxB -> (A1,B1);(A1,B2);(A2,B1);(A2,B2);
Now we are ready for the final touch.
CROSS JOIN UNPIVOT
Now let's combine both:
I wish to CROSS JOIN
columns A,B with the result of UNPIVOT
of the columns (C,E) and (D,F):
Once the logic itself is clear the implementation will be clear as well, hence if you need to understand the concept I'm inviting you to read it through few times and to get the idea of the both operations used to manipulate the data first separately and then in combination.
Enjoy and stay tuned!
Top comments (0)