SQL Pro tips : CROSS JOIN UNPIVOT summary for beginners

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 :

• Oracle XE
• BigQuery
• Athena

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!