# Students Report By Geography

• Click here to see the full article post

• May I know if case when can be used here?
I try to do:

``````SELECT (CASE WHEN continent = 'America' then name else NULL END) AS America, (CASE WHEN continent = 'Asia' then name else NULL END) AS Asia, (CASE WHEN continent = 'Europe' then name else NULL END) AS Europe FROM student
``````

But I don't know how to remove the null values

• @pxj5333 I don't think you can use `case...when...`. Please check the editorial solution instead.

• SELECT a.america, b.asia, c.europe
FROM (SELECT ROWNUM rid, name AS America
FROM student
WHERE continent = 'America') A,
(SELECT ROWNUM rid, name AS Asia
FROM student
WHERE continent = 'Asia') B,
(SELECT ROWNUM rid, name AS Europe
FROM student
WHERE continent = 'Europe') C
WHERE a.rid = b.rid(+) AND b.rid = c.rid(+);

• well, and what to do if we have not continents, but countries or cities? Wall of constants doesn't look like a solution

• @vineeshbnair unknown column ROWNUM error

• Easier to use window function row_number() with PostgreSQL:

``````SELECT
America,
Asia,
Europe
FROM
(SELECT
row_number()
OVER (PARTITION BY continent) AS asid,
name                          AS Asia
FROM
student
WHERE
continent = 'Asia'
ORDER BY Asia) AS t1
RIGHT JOIN
(SELECT
row_number()
OVER (PARTITION BY continent) AS amid,
name                          AS America
FROM
student
WHERE
continent = 'America'
ORDER BY America) AS t2 ON asid = amid
LEFT JOIN
(SELECT
row_number()
OVER (PARTITION BY continent) AS euid,
name                          AS Europe
FROM
student
WHERE
continent = 'Europe'
ORDER BY Europe) AS t3 ON amid = euid;
``````

• @pxj5333 case ...... when ... will always give you null values in this case, so it is not a good solution here.

• with dataset as (
select row_number() over (partition by continent order by continent) rid,continent,name from student
)
select
decode(am.continent,'America',am.name,null) as "America"
,decode(asia.continent,'Asia',asia.name,null) as "asia"
,decode(eu.continent,'Europe',eu.name,null) as "Europe"
from
(select * from dataset where continent='Europe' ) eu
full join
(select * from dataset where continent='America' ) am
on eu.rid=am.rid
full join
(select * from dataset where continent='Asia' ) asia
on eu.rid=asia.rid

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.