QUESTION:

ANSWER:

set @r1=0, @r2=0, @r3=0, @r4=0;
select min(Doctor), min(Professor), min(Singer), min(Actor)
from(
  select case when Occupation='Doctor' then (@r1:=@r1+1)
            when Occupation='Professor' then (@r2:=@r2+1)
            when Occupation='Singer' then (@r3:=@r3+1)
            when Occupation='Actor' then (@r4:=@r4+1) end as RowNumber,
    case when Occupation='Doctor' then Name end as Doctor,
    case when Occupation='Professor' then Name end as Professor,
    case when Occupation='Singer' then Name end as Singer,
    case when Occupation='Actor' then Name end as Actor
  from OCCUPATIONS
  order by Name
) as Temp
group by RowNumber

Doctor	Professor	Singer	Actor
Aamina	Ashley	Christeen	Eve
Julia	Belvet	Jane	Jennifer
Priya	Britney	Jenny	Ketty
NULL	Maria	Kristeen	Samantha
NULL	Meera	NULL	NULL
NULL	Naomi	NULL	NULL
NULL	Priyanka	NULL	NULL

---

---

---

---

---

set @r1=0, @r2=0, @r3=0, @r4=0;
select case when Occupation='Doctor' then (@r1:=@r1+1)
            when Occupation='Professor' then (@r2:=@r2+1)
            when Occupation='Singer' then (@r3:=@r3+1)
            when Occupation='Actor' then (@r4:=@r4+1) end as RowNumber,
    case when Occupation='Doctor' then Name end as Doctor,
    case when Occupation='Professor' then Name end as Professor,
    case when Occupation='Singer' then Name end as Singer,
    case when Occupation='Actor' then Name end as Actor
  from OCCUPATIONS
  order by Name

OUTPUT:

Row Number	Doctor	Professor	Singer	Actor
1	NULL	Ashley	NULL	NULL
2	NULL	Belvet	NULL	NULL
3	NULL	Britney	NULL	NULL
1	NULL	NULL	Christeen	NULL
1	NULL	NULL	NULL	Eve
2	NULL	NULL	Jane	NULL
2	NULL	NULL	NULL	Jennifer
3	NULL	NULL	Jenny	NULL
2	Julia	NULL	NULL	NULL
3	NULL	NULL	NULL	Ketty
4	NULL	NULL	Kristeen	NULL
4	NULL	Maria	NULL	NULL
5	NULL	Meera	NULL	NULL
6	NULL	Naomi	NULL	NULL
3	Priya	NULL	NULL	NULL
7	NULL	Priyanka	NULL	NULL
4	NULL	NULL	NULL	Samantha