Occupations

Link

Tables

erDiagram
	occupations {
		Name String
		Occupstion String
	}
Mermaid
복사

DataFlow

•

left join or right join의 경우 기준이 바뀌지 않도록 join하는 방법

•

pivot 문제 -> row number 사용

Answer

WITH d AS (
	SELECT
		Name,
		ROW_NUMBER() OVER(ORDER BY Name ASC) AS rn
	FROM Occupations
	WHERE Occupation = 'Doctor'
),
p AS (
	SELECT
		Name,
		ROW_NUMBER() OVER(ORDER BY Name ASC) AS rn
	FROM Occupations
	WHERE Occupation = 'Professor'
),
s AS (
	SELECT
		Name,
		ROW_NUMBER() OVER(ORDER BY Name ASC) AS rn
	FROM Occupations
	WHERE Occupation = 'Singer'
),
a AS (
	SELECT
		Name,
		ROW_NUMBER() OVER(ORDER BY Name ASC) AS rn
	FROM Occupations
	WHERE Occupation = 'Actor'
),
r AS (
	SELECT rn FROM d
	UNION
	SELECT rn FROM p
	UNION
	SELECT rn FROM s
	UNION
	SELECT rn FROM a
)

SELECT
	d.Name AS Doctor,
	p.Name AS Professor,
	s.Name AS Singer,
	a.Name AS Actor
FROM r
LEFT JOIN d
	ON r.rn = d.rn
LEFT JOIN p
	ON r.rn = p.rn
LEFT JOIN s
	ON r.rn = s.rn
LEFT JOIN a
	ON r.rn = a.rn
ORDER BY r.rn;
SQL
복사