Visualize SQL | Report Formatting(part 4)

# Beginning
This technique is based on SQL Puzzle and Answers # 62 REPORT FORMATTING
It is better with 『SQL Puzzle and Answers』 to read this article.

It explains the idea of algorithm and
You can check what each puzzle is intended for ・・・

This puzzle is very simple

There is a table of name list
and display names like two across, three across

・・・ it is easy to say
but we do not do this kind of thing with SQL

The author, Mr Joe Celko, also says


> **quoted from the book**
>
>SQL is a data retrieval language and not a report writer
>Unfortunately, people do not seem to know this
>and are always trying to do things for which
>SQL was not intended ・・・・

『part4』 is the last article of this puzzle

This SQL also use MOD function but only once
Taking out the second and third name is very technical・・・
I mean, how come up with this kind of idea!!

I'm just impressed

Let's start
We are going to display 『names four across』 this time


**I have checked that it works with PostgreSQL**

▼ Output format is like below image

![re_1.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/56436145-cb59-e66d-60c8-7eda05445d42.jpeg)

# Table and data
```SQL:SQL
CREATE TABLE Names(
	name VARCHAR(15) NOT NULL PRIMARY KEY
);

INSERT INTO Names
VALUES ('Al'), 
	   ('Ben'),
	   ('Charlie'),
       ('David'),
	   ('Ed'),
	   ('Frank'),
       ('Greg'),
	   ('Howard'),
	   ('Ida'),
       ('Joe'),
	   ('Ken'),
	   ('Larry'),
       ('Mike'),
	   ('Neal');
```

▼ Output

![re_2.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/7bb237fd-9397-7a29-c674-a2d4171242e9.jpeg)

# Creating original data ①
```SQL:SQL
SELECT N1.name, N2.name
FROM Names AS N1, Names AS N2
WHERE N1.name >= N2.name
```

INNER JOIN is used to create original data in 『part2』and『part3』
> SELECT N1.name, N2.name
> FROM Names AS N1
> INNER JOIN Names AS N2 ON **N1.name >= N2.name**

CROSS JOIN is used to create original data in 『part4』
condition is the same AS 『N1.name >= N2.name』

Same result, but written differently

N1.name and N2.name are lined vertically ・・・
but they are rearranged horizontally on purpose

Please check 『part2』and『part3』 for the details of the original data

![re_3.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/e1a23de0-7b07-a35c-c30b-e50e20bf0bf3.jpeg)

-------

▼ Output

![re_4.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/9ee0818d-2bba-a69e-69c3-f930f197b6cc.jpeg)



# Edit original data ①
```SQL:SQL
SELECT N1.name,COUNT(*),MOD(COUNT(*), 4)
FROM Names AS N1, Names AS N2
WHERE N1.name >= N2.name
GROUP BY N1.name
```

Add COUNT(＊) in SELECT statement
　⇒　N1.name is aggregated (GROUP BY N1.name)
　⇒　COUNT(＊) is the number of rows that N1.name has
　⇒　It is the same as the number of N2.name that are joined to N1.name

Add MOD(COUNT(＊), 4) in SELECT statement
　⇒　remainder of (COUNT(＊) ÷ 4)


![re_5.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/53184f45-9241-4d7d-622a-a8eaee08fa01.jpeg)

----

▼ Output

Up to this point, it is the same as 『part2』and『part3』
It is a preparation that we have seen

『part4』 changes from here

To take out the 2nd, 3rd and 4th name
we are going to create another original data next


![re_6.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/ddfb9f1f-1522-7fca-e66a-9f654a7b9592.jpeg)


# Creating original data2
```SQL:SQL
SELECT N3.name, N4.name, N5.name
FROM Names AS N3, Names AS N4, Names AS N5
WHERE N3.name < N5.name 
```

CROSS JOIN 3 Names table
　⇒　14 x 14 x 14 = 2744 rows data are joined
　⇒　1274 rows are left due to 『N3.name < N5.name』condition

Since there are many rows, I will explain using asmall parts

The following rows are excluded(right side of image)
　⇒　conbinations of 『N5.name is Al』(green)
　⇒　conbinations of 『N3.name is Neal』(blue)
　⇒　conbinations of 『N3.name > N5.name』(yellow)

▼ Output(left side of image)

we do not see the purpose of this original data yet ・・・

![re_7.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/0ba7839d-7f67-e7c7-c949-fe66eec3a811.jpeg)


# Edit original data2 ①
```SQL:SQL
SELECT N3.name, N4.name, N5.name
FROM Names AS N3, Names AS N4, Names AS N5
WHERE N3.name < N5.name 
  AND N4.name BETWEEN N3.name AND N5.name

order by N3.name, N5.name, N4.name
```

Add another condition
　⇒　`N4.name BETWEEN N3.name AND N5.name`
　⇒　names(N4.name) which is between N3.name and N5.name are left
　⇒　N4.name is the same as N.3.name or N5.name also fit this condition

▼ Output(right side of image)

We see something like pattern ・・・
It looks N3.name, N4.name, N5.name is lined in alphabetical order

There is a pattern in the number of rows after adding this condition
Al's case, 2 rows -> 3 rows -> 4 rows are left from the top

> **about N4.name**
> let's say N3.name is the 1st name
> 2nd, 3rd and 4th name are taken from N5.name
> the number of N5.names joined to N3.name is serial from the top
> to make this number use N4.name
>
> I will explain this serial number next

![re_8.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/5aded07b-f200-f394-b359-69a4f8063846.jpeg)


# Edit original data2 ②
```SQL:SQL
SELECT N3.name, N5.name, COUNT(*)
FROM Names AS N3, Names AS N4, Names AS N5
WHERE N3.name < N5.name 
  AND N4.name BETWEEN N3.name AND N5.name
GROUP BY N3.name, N5.name

order by N3.name,N5.name
```

Add 『GROUP BY N3.name,N5.name』　then count rows
There is not N4.name because it is not needed

Looking at the whole, there is regularity
**COUNT (*) is a serial number**
What we want to do with this second original data is ・・・
　⇒　to prepar `to pick 2nd,3rd and 4th names using N5.name`
　⇒　`to create the serial number` to pick the names

Al's case
　⇒　Al is picked as 1st name
　⇒　the next is Ben. COUNT(＊) is 2
　⇒　the next is Charlie. COUNT(＊) is 3
　⇒　the next is David. COUNT(＊) is 4
　⇒　the next is Frank. COUNT(＊) is 5
　　　・　　・　　・　　・　　・
　　　・　　・　　・　　・　　・

Ben's case
　⇒　Ben is picked as 1st name
　⇒　the next is Charlie. COUNT(＊) is 2
　⇒　the next is David. COUNT(＊) is 3
　⇒　the next is Frank. COUNT(＊) is 4
　⇒　the next is Greg. COUNT(＊) is 5
　　　・　　・　　・　　・　　・
　　　・　　・　　・　　・　　・

other names are also the same as Al or Ben

**This SQL uses 『CROSS JOIN』 3 times then narrow the rows we need**
**to create the data which has this regularity**

![re_9.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/febcbf73-ccb5-2337-7ef6-f5693110f852.jpeg)


# Edit original data2 ③
```SQL:SQL
SELECT FirstCol.name AS name1,
       OtherCols.final_name,
       OtherCols.cnt
FROM 
(
  SELECT N1.name
  FROM Names AS N1, Names AS N2
  WHERE N1.name >= N2.name
  GROUP BY N1.name
  HAVING MOD(COUNT(*), 4) = 1
) AS FirstCol(name)
LEFT JOIN
(
  SELECT N3.name, N5.name, COUNT(*)
  FROM Names AS N3, Names AS N4, Names AS N5
  WHERE N3.name < N5.name AND N4.name BETWEEN N3.name AND N5.name
  GROUP BY N3.name, N5.name
) AS OtherCols(name,final_name, cnt) 
     ON FirstCol.name = OtherCols.name

order by FirstCol.name,OtherCols.final_name
```

**We are going to join 2 original data** that we created before

taking out the 1st name from 『Edit original data ①』using HAVING
　⇒　subquery: `FirstCol`
　⇒　the rows 『MOD(COUNT(＊), 4)』 returns 1
　⇒　Al, Ed, Ida, Mike fit this condition

![re_10.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/fc8f6172-044f-b91c-51d6-034e5f185df7.jpeg)


LEFT JOIN 『Edit original data2 ②』 data(`OtherCols`) to Al, Ed, Ida and Mike
who are taken from `FirstCol`

![re_11.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/214f9edc-f794-8251-b029-a04fce4b3921.jpeg)


-----

▼ Output(left side of image)

name(name1) which is taken from FirstCol is the 1st name
We are going to take out 2nd, 3rd, 4th name from OtherCols ・・・
but needed to edit this SQL a little more

right side of image is the way to pick other names

we are very close to the goal !!


![re_12.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/3c5d097b-5a8c-79b2-a57f-5a54ddf934ab.jpeg)


# Answer SQL
```SQL:SQL
SELECT FirstCol.name AS name1,
       MAX(CASE WHEN OtherCols.cnt = 2
                THEN OtherCols.final_name
                ELSE NULL END) AS name2,
       MAX(CASE WHEN OtherCols.cnt = 3
                THEN OtherCols.final_name
                ELSE NULL END) AS name3,
       MAX(CASE WHEN OtherCols.cnt = 4
                THEN OtherCols.final_name
                ELSE NULL END) AS name4
FROM 
(
	SELECT N1.name
	FROM Names AS N1, Names AS N2
	WHERE N1.name >= N2.name
	GROUP BY N1.name
	HAVING MOD(COUNT(*), 4) = 1
) AS FirstCol(name)
LEFT JOIN
(
	SELECT N3.name, N5.name, COUNT(*)
	FROM Names AS N3, Names AS N4, Names AS N5
	WHERE N3.name < N5.name AND N4.name BETWEEN N3.name AND N5.name
	GROUP BY N3.name, N5.name
) AS OtherCols(name,final_name, cnt) 
     ON FirstCol.name = OtherCols.name
GROUP BY FirstCol.name
```

▼ Output

![re_13.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/c81f9588-62a0-b9fa-7b57-cadf10ce965f.jpeg)

----

FirstCol.name is aggregated(GROUP BY FirstCol.name)
so it becomes unique name. This is 1st name

2nd, 3rd and 4th names are taken from OtherCols ・・・
but N5.name(final_name) is not the aggregate variable
so use MAX function to take out each name

**The names that are lined up vertically are changed to horizontally**
**using CASE statement**

Al's case
　⇒　1st name is Al
　⇒　2nd name is Ben(cnt is 2). Display it next to Al
　⇒　3rd name is Charlie(cnt is 3). Display it next to Ben
　⇒　4th name is David(cnt is 4). Display it next to Charlie
　⇒　cnt is more than 5, display nothing

below is the image of how to pick names using CASE


![re_14.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/b1b85a05-0ae9-ac1e-65e9-1158dd959086.jpeg)



> Personally ・・・
> 
> I could see the author's enthusiasm from 『part4』
> OtherCols that takes out 2nd ～ 4th names is wonderful
>
> This engineer can write this SQL
> because he knows how to join data very well
>
> salute!!

# References 
[Joe Celko's SQL Puzzles and Answers](https://www.amazon.com/Puzzles-Answers-Kaufmann-Management-Systems/dp/0123735963)