365 Days of Daily Coding: Day 2

Months of pondering and I am finally embarking on this journey to solve at least one coding challenge a day.

I was reluctant to post about my Day 1 progress but I thought it would be a good idea to document my progress and therefore, somewhere down the line; when I look back, I would appreciate the journey I have gone through. It’s been a rough journey. The starting of the journey was filled with disappointment, frustration and still is. However, with perseverance and grit, I believe I will get there.

I am doing challenges in HackerRank for the start. Today’s challenge really got the best of me.

Given a sample of output in the table name Occupations, I had to pivot the Occupation column so that each name; sorted alphabetically, is displayed under their respective occupation.

Table:

Name	Occupation
Samantha	Doctor
Julia	Actor
Maria	Actor
Meera	Singer
Ashley	Professor

Expected Output:

Doctor	Actor	Singer	Professor
Samantha	Julia	Meera	Ashley
Null	Maria	Null	Null

Solution:

 SELECT Doctor, Professor, Singer, Actor
 FROM (SELECT *
      FROM
      (SELECT Name, Occupation,
      (ROW_NUMBER() OVER(PARTITION BY Occupation ORDER BY Name)) 
      AS row_num
      FROM Occupations
      ORDER BY Name)
      PIVOT (MIN(Name) FOR Occupation in 
      ('Doctor' AS Doctor, 'Professor' AS Professor, 
      'Singer' AS Singer, 'Actor' As Actor))
      ORDER BY row_num);

I used help from the discussion board for the solution as well as to understand the process of how the above solution worked. I have tried to break down the process into several queries to try to explain how the query works.

Query 1:

We can use the Pivot function available in oracle to cross tabulate as well as transpose rows into columns. The oracle documentation to the pivot function is here.

With the following query, the occupation becomes the headers and the aggregate function min takes the first names in the occupation

 SELECT Doctor, Professor, Singer, Actor
 FROM Occupations
 PIVOT
 (MIN(Name) FOR occupation IN ('Doctor' AS Doctor, 'Professor' AS Professor, 'Singer' AS Singer, 'Actor' As Actor)); 

Output:

Julia

Samantha

Ashley

Meera

Query 2:

With the following query, all the names under the occupation are categorised with row numbers.

SELECT Name, Occupation , ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS  row_num FROM Occupations;

Output:

Julia	Actor	1
Maria	Actor	2
Samantha	Doctor	1
Ashley	Professor	1
Meera	Singer	1

Query 3:

With the combination of query 1 and query 2 , you are able to pivot the tables with the occupation as the column header and the row_num as the row headers.

 SELECT *
      FROM
      (SELECT Name, Occupation,
      (ROW_NUMBER() OVER(PARTITION BY Occupation ORDER BY Name)) AS row_num
      FROM Occupations
      ORDER BY Name)
      PIVOT (MIN(Name) FOR Occupation in ('Doctor' AS Doctor, 'Professor' AS Professor, 'Singer' AS Singer, 'Actor' As Actor))
      ORDER BY row_num; 

Output:

1	Julia	Samantha	Ashley	Meera
2	Maria	Null	Null	Null

Query 4:

Finally, SELECT Doctor, Professor, Singer, Actor FROM selects the column headers leaving the row_nums.

Link to the HackerRank challenge is here.

If you would like to connect, feel free to reach out to me via my social media.

• Facebook
• Instagram
• LinkedIn
• GitHub

1. 

   Hey Kiran, Thanks for your comment. I would advise you to go through the resources below and choose any links…

2. 

   Hi can you post link to the questions/codes you practice? Im looking to practice my SQL as well. DO not…

3. 

   👍 cool

4. 

   Hi Helo Karma, thank you for your post about this series. It also affected me quite deeply, even though I…

5. 

   Oh gosh! hmmm.. I am kind of lost for words really 😅😅😅 .Thank you for your kind words. My motivation…