with pv_dt as ( select *, row_number() over (partition by occupation order by name) as nm from occupations ) select [Doctor], [Professor], [Singer], [Actor] from pv_dt pivot (max(name) for occupation in ([Doctor], [Professor], [Singer], [Actor])) as p;

with cte as (select *, row_number() over(partition by occupation order by name) rn from occupations)

select max(case when occupation = 'Doctor' then name end) as doctor
, max(case when occupation = 'Professor'then name end )as professor , max(case when occupation ='Singer' then name end ) as singer , max(case when occupation = 'Actor' then name end) as actor from cte group by rn order by rn

select max(case when occupation='Doctor' then name end) as DOCTOR, max(CASE WHEN OCCUPATION= 'Professor' then name end) as Profrssor, max(CASE WHEN OCCUPATION= 'Singer' then name end) as Singer , max(CASE WHEN OCCUPATION= 'Actor' then name end) as Actor from ( select NAME,OCCUPATION,row_number()over(partition by occupation order by name) rn from occupations) group by rn order by rn;

with CTE1 as ( select distinct name as Doctor_name, row_number() over(order by name asc) as rn from OCCUPATIONS where OCCUPATION = 'Doctor' ), CTE2 As ( select distinct name as professor_name, row_number() over(order by name asc) as rn from OCCUPATIONS where OCCUPATION = 'Professor' ), CTE3 As ( select distinct name as Singer_name, row_number() over(order by name asc) as rn from OCCUPATIONS where OCCUPATION = 'Singer' ), CTE4 As ( select distinct name as actor_name, row_number() over(order by name asc) as rn from OCCUPATIONS where OCCUPATION ='Actor' ) select Doctor_name, professor_name, Singer_name, actor_name from CTE1 full outer join CTE2 on CTE1.rn = CTE2.rn full outer join CTE3 on CTE2.rn = CTE3.rn full outer join CTE4 on CTE3.rn = CTE4.rn I am getting runtime error

order by 1, 2, 3, 4

SELECT MIN(IF(occupation = "Doctor", name, NULL)) AS "Doctor", MIN(IF(occupation = "Professor", name, NULL)) AS "Professor", MIN(IF(occupation = "Singer", name, NULL)) AS "Singer", MIN(IF(occupation = "Actor", name, NULL)) AS "Actor" FROM ( SELECT name, occupation, ROW_NUMBER() OVER(PARTITION BY occupation ORDER BY name) AS indexing FROM occupations ) AS short_table GROUP BY indexing