SQL Pro tips : Oracle SQL CROSS JOIN with UNPIVOT
Tags: sql, oracle, bigdata, database
Published: 2023-09-28 15:44:58

## 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.
Disclaimer : in the example below `WEEK_NUMBER` is not a suitable identifier for the `varchar` type, the better name is `WEEK_REF` and the variables should better be prefixed with year, like `2024W01`
## Setup
Feel free to check the [Oracle XE Quick Start](https://dev.to/hexfloor/sql-pro-tips-oracle-xe-quickstart-jei)
I will be using `docker` enabled `Oracle XE 21.3.0` with `DBeaver` as a client.
## UNPIVOT
Before starting, I'm inviting you to check a wonderful [article from Oracle](https://www.oracle.com/technical-resources/articles/database/sql-11g-pivot.html) regarding `PIVOT`and `UNPIVOT`. If this article does exist, why to write another one? The answer is simple : because this article is a step-by-step guide which will help you to understand over copy-paste.
While writing this article I have found another [good article about unpivot](https://www.oracletutorial.com/oracle-basics/oracle-unpivot/), check it out !

What is the business problem we are trying to solve : as a result of an aggregation over a big chunk of data we have a dataset composed with rows with multiple columns that we would like to stack one below another, probably with the common prefix for each chunk of the row stacked in the new representation.
Let's have an example : 

```
SELECT 
	'SUNDAY' AS DAY_0,
	'MONDAY' AS DAY_1
FROM DUAL;
```


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8scosz4zhk6fec9lt6cy.png)
Simple stacking with `UNION ALL` : 

```
WITH 
	DAY_DATA AS 
		(SELECT 
			'SUNDAY' AS DAY_0,
			'MONDAY' AS DAY_1
		FROM DUAL)
SELECT DAY_0 AS DAY_NAME FROM DAY_DATA
UNION ALL 
SELECT DAY_1 AS DAY_NAME FROM DAY_DATA;

```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vlsjcda3ff8v6uolq6gm.png)


And the same result with `UNPIVOT` :  

```
SELECT DAY_NAME FROM (
	(SELECT 
		'SUNDAY' AS DAY_0,
		'MONDAY' AS DAY_1
	FROM DUAL)
	UNPIVOT (
		DAY_NAME FOR DAY_COLUMN IN (DAY_0, DAY_1)
	)
)
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jolazxcb0zh8p3u7sf2k.png)
Great ! Let's move on and add two more columns that we would like to stack : 

```
SELECT 
	'SUNDAY' AS DAY_0,
	0 AS DAY_ORDER_0,
	'MONDAY' AS DAY_1,
	1 AS DAY_ORDER_1
FROM DUAL
```
 
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4m8b9jstv6xqmmscphmi.png)
Stacking values from the columns `DAY_0` and `DAY_1` into the column `DAY_NAME` and values from the columns `DAY_ORDER_0` and `DAY_ORDER_1` into the column `DAY_ORDER` with `UNION ALL` : 

```
WITH 
	DAY_DATA AS 
		(SELECT 
			'SUNDAY' AS DAY_0,
			1 AS DAY_ORDER_0,
			'MONDAY' AS DAY_1,
			1 AS DAY_ORDER_1
		FROM DUAL)
SELECT DAY_0 AS DAY_NAME, DAY_ORDER_0 AS DAY_ORDER FROM DAY_DATA
UNION ALL 
SELECT DAY_1 AS DAY_NAME, DAY_ORDER_1 AS DAY_ORDER FROM DAY_DATA;

```
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/suqf9jkgulz3ncds3q28.png)

And the same result with `UNPIVOT` 

```
SELECT * FROM (
	(SELECT 
		'SUNDAY' AS DAY_0,
		1 AS DAY_ORDER_0,
		'MONDAY' AS DAY_1,
		1 AS DAY_ORDER_1
	FROM DUAL)
	UNPIVOT (
		(DAY_NAME, DAY_ORDER) 
		FOR DAY_COLUMN IN (
			(DAY_0, DAY_ORDER_0) AS 'DAY_0',
			(DAY_1, DAY_ORDER_1) AS 'DAY_1'
		)
	)
)
```


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kv0gqbixangzc8sryzom.png)

And when we have a common classifier : 

```
SELECT 
	WEEK_NUMBER,
	DAY_NAME,
	DAY_ORDER
FROM (
	(SELECT 
		'W01' AS WEEK_NUMBER,
		'SUNDAY' AS DAY_0,
		1 AS DAY_ORDER_0,
		'MONDAY' AS DAY_1,
		1 AS DAY_ORDER_1
	FROM DUAL)
	UNPIVOT (
		(DAY_NAME, DAY_ORDER) 
		FOR DAY_COLUMN IN (
			(DAY_0, DAY_ORDER_0) AS 'DAY_0',
			(DAY_1, DAY_ORDER_1) AS 'DAY_1'
		)
	)
)
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sodbcfzb2mo2e3l3lebh.png)
That's it !

## CROSS JOIN
Example above uses implicit [CROSS JOIN](https://en.wikipedia.org/wiki/Join_(SQL)) under the hood, you may check an [Oracle article about CROSS JOIN](https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljcrossjoin.html)
Firstly let's have a simple example of `CROSS JOIN` :

```
WITH WEEK_DATA AS (
	SELECT 
		'W01' AS WEEK_NUMBER 
	FROM DUAL),
	DAY_DATA AS (
		SELECT 'SUNDAY' AS DAY_NAME FROM DUAL
	)
SELECT * FROM WEEK_DATA CROSS JOIN DAY_DATA;


```


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9l12psjvvz8e50zx6jhl.png)



Adding another row to the `DAY_DATA`: 

```
WITH WEEK_DATA AS (
	SELECT 
		'W01' AS WEEK_NUMBER 
	FROM DUAL),
	DAY_DATA AS (
		SELECT 'SUNDAY' AS DAY_NAME FROM DUAL
		UNION ALL
		SELECT 'MONDAY' AS DAY_NAME FROM DUAL
	)
SELECT * FROM WEEK_DATA CROSS JOIN DAY_DATA;

```


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/e2xmot1zkpm408iypgda.png)

Adding another row to the `WEEK_DATA` :

```
WITH WEEK_DATA AS (
		SELECT 'W01' AS WEEK_NUMBER	FROM DUAL
		UNION ALL 
		SELECT 'W02' AS WEEK_NUMBER FROM DUAL
	),
	DAY_DATA AS (
		SELECT 'SUNDAY' AS DAY_NAME FROM DUAL
		UNION ALL
		SELECT 'MONDAY' AS DAY_NAME FROM DUAL
	)
SELECT * FROM WEEK_DATA CROSS JOIN DAY_DATA;

```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ycw8g4sif2gbzm65614q.png)

## CROSS JOIN with UNPIVOT
Let's combine both `CROSS JOIN` and `UNPIVOT` : 

```
WITH WEEK_DATA AS (
		SELECT 'W01' AS WEEK_NUMBER	FROM DUAL
	),
	DAY_DATA AS (
		SELECT 
			DAY_NAME,
			DAY_ORDER
		FROM (
			(SELECT 
				'SUNDAY' AS DAY_0,
				1 AS DAY_ORDER_0,
				'MONDAY' AS DAY_1,
				1 AS DAY_ORDER_1
			FROM DUAL)
			UNPIVOT (
				(DAY_NAME, DAY_ORDER) 
				FOR DAY_COLUMN IN (
					(DAY_0, DAY_ORDER_0) AS 'DAY_0',
					(DAY_1, DAY_ORDER_1) AS 'DAY_1'
				)
			)
		)
	)
SELECT * FROM WEEK_DATA CROSS JOIN DAY_DATA;

```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/w1a7q5pkv5ia6b10dpox.png)

Now you know how to combine both `CROSS JOIN` and `UNPIVOT`, the concepts explained above are semantically the same for the Big Data solutions hosted in different cloud providers, I will show few examples in the following articles. Stay tuned !