Thanks!

HEY, what does temp do?

such a pellucid explanation. Thank you :)

not work

Thank you for your explanation. I did not uderstand why we use min/max.Please explain

Thanks a bunch for this!!

Can you explain what exactly is happening in step2? What does 'set @' do? I tried running just the code in step 2 but couldn't understand.

wow! this is the best explanation i got so far.. just a few things why did we use min() and groupby rownumber.

wow! this is the best explanantion i found so far. just a few queries: why are we using min() and groupby rownumber in the end??

Awesome explanation! Much appreciated :)

Thanks for the breakdown, it really helps me to understand each concept.

hey, can someone explain why we use min/max here? thanks

Thanks a lot :) crisp explanation

Can you please explain what is use of "END AS" in line:: case when Occupation='Doctor' then Name end as Doctor

woooh!

You can improve readability using window functions

WITH pivot AS (
    SELECT
        ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY name) AS rn,
        IF(occupation='Doctor', name, NULL)     AS doctor,
        IF(occupation='Professor', name, NULL)  AS professor,
        IF(occupation='Singer', name, NULL)     AS singer,
        IF(occupation='Actor', name, NULL)      AS actor
    FROM occupations
)

SELECT MIN(doctor), MIN(professor), MIN(singer), MIN(actor)
FROM pivot
GROUP BY rn;

Thanks for the explaination, that's great!

Thank you so much for explaination and posting this :)

Thanks

Greate explanation, Thanks

Brilliant. Thanks !

Thanks for such a nice explanation

Thank you so much!!

Pretty clear, thanks!

Thankyou so much!

Hi, your code is giving me an error. Please help

unknown SET option "@r1=0," select case when Occupation='Doctor' then (@r1:=@r1+1) * ERROR at line 3: ORA-00936: missing expression

Wonderful explanation!! Thanks!

Great crack! Thank you btw!!

Great explaination thank you for writing this!

Thanks! Helped a lot

thanks so much

Here, can't we use pivot ? If not then WHY?

thank you for your explanation raiyanger24, I learn a lot

WOW!!!

Excellant Explanation! Opened me to new type of problem solving. Thank you :D

Amazing! Thanks

what does the @r1=0 to @r4=0 means?

Very good explanation

great, thank you.

Thanks so much for a very clear explanation!

Thank you very much for the steps. Learned how to pivot rows and columns using mysql from your post.

BEST EXPLANATION..THANKS A TON!

Thanks! for detailed explanation

One of the most detailed explanation ever! Thanks.

Thanks for breaking down the problem so nicely that people could understand easily.

Too good man! Thanks

thanks! This is so helpful and informative! Great explanation.

thanks, sql magician!

Thanks!!Good Work

Great explaination, i had an idea to do like that but can't find a way to make it, you explained everything perfectly clear. Thank you!

Thank you for explaining it so beautifully. This is excellent for beginners.

Thank you so much!. Your explanation is awsome, I culdn't figure the solution out with out your example

But my output is comig wrong. Please tell me what to do. OUTPUT: Your Output (stdout) Aamina Ashley Christeen Eve

Thanks for the answer and clear explination

I'm a beginner and you saved my day, thanks

Thnx for the qeury

This is crazy detailed and clear. Thanks a lot!!

This has got to be the best explained comment in all of hacker rank i have seen yet. Kudos to you for going the extra mile to explain to us mere mortals.

This is briliant, thanks!

Really great explanation! Thanks!

I have some commnet about how to keep track at each row. How does it look when we use :

ROW_NUMBER() OVER(PARTITION BY(Occupation) ORDER BY(Name))

This is window function that automatically indexing each row follow our condition, it contain the same mechanism when we use

set @r1=0 ... case .... @r1 := @r1 + 1

Since I see that we hasn't nessecery user variable here, I think we can simplify and avoid to use user variable where it really doesn't ineeded

Thank you!

thanks:) It helped me a lot

Thank you so much, it is so clear and really explains a lot.

While I have a small doubt about the order by clause. Could anyone explain why the RowNumber that is set in the 'case when' statement will be sorted again? I thought that the ORDER BY clause is operated after the SELECT statement, so should not the RowNumber be set with the rows that name was not sorted yet? Exemplifying from raiyanger24's picture example that - Julia will still be in RowNumber of 1 for Doctor.even after an order by execution Thank you so much guys!

You could've skipped everything upto step 3 with the help of window function.

SELECT
	min(Doctor),
	min(Professor),
	min(Singer),
	min(Actor)
FROM
	(
	SELECT
		ROW_NUMBER() OVER (PARTITION BY OCCUPATION
	ORDER BY
		NAME) AS r,
		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
) t
GROUP BY t.r

Regardless, your comment was very helpful for me.

Can you explain this SELECT MIN(Doctor),MIN(Professor),MIN(Singer),MIN(Actor) why we are selecting min of all columns?

thanks for the kind explation!

Thanks a lot, every beautiful and clear explanation. Do you have any youtube channel or blog about SQL or coding or data stuff,.. I wanna subcribe yours:))

awsome explanation just so up to point thanks a lot

Adding my voice to the chorus of thanks for this - thank you! It would have taken me a very long time to figure that out alone - I've really learned something from this exercise and your explanation.

tysm

Thank you!

phenomenal answer!

Very good answer