Pivot data to Multiple columns in MySQL

Question

I am trying to solve a question on HackerRank.

Here is my approach to solve the question:

select Concat(Name, '(', Substring(Occupation,1,1), ')') as pr
from Occupations
order by name;

select Concat('There are a total of',' ', count(*),' ',Occupation, 's.') as pr
from Occupations
group by Occupation
order by pr

This is the sample output of my query:

Ashely(P)
Christeen(P)
Jane(A)
Jenny(D)
Julia(A)
Ketty(P)
Maria(A)
Meera(S)
Priya(S)
Samantha(D)
There are a total of 2 doctors.
There are a total of 2 singers.
There are a total of 3 actors.
There are a total of 3 professors.

Why couldn't my code generate expected output?

Answer 1

Your approach of solving a problem was good. You can use this approach and it works in mysql but it doesn't work in oracle. Because, in Oracle, CONCAT function can take a maximum of 2 arguments to concatenate where as in mysql, CONCAT function can take multiple arguments. You can use nested concat statements as follows:

select concat(concat('There are a total of ', count(*)), concat(' ', occupation)) as pr from occupations group by occupation order by pr;

Else, use || to concat multiple strings as below:

select ('There are a total of '|| count(*) || ' ' || occupation || 's') as pr from occupations group by occupation order by pr;

Answer 2

To solve the given question, you would need to create columns from the records in the table, but, SQL doesn't have a native PIVOT function like some other technologies, though we can simulate it. Here's one way to pivot our data:

SELECT
    MAX(CASE WHEN Occupation = 'Doctor' THEN Name ELSE NULL END) AS Doctor,
    MAX(CASE WHEN Occupation = 'Professor' THEN Name ELSE NULL END) AS Professor,
    MAX(CASE WHEN Occupation = 'Singer' THEN Name ELSE NULL END) AS Singer,
    MAX(CASE WHEN Occupation = 'Actor' THEN Name ELSE NULL END) AS Actor
FROM
    (SELECT Name, Occupation, ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS rn
     FROM Occupations) AS sub
GROUP BY rn
ORDER BY rn;

The code you've written serves a different purpose than the task of pivoting the table as described in the question.

Answer 3

I think you need to add only GROUP_CONCAT function

SELECT GROUP_CONCAT(concat(Name, '(', Substring(Occupation, 1, 1), ')') SEPARATOR ' ') pr FROM Occupations; 

SELECT GROUP_CONCAT(Concat('There are a total of ', cnt,' ', Occupation, '.') SEPARATOR ' ') pr FROM (
    SELECT Occupation, count(*) cnt FROM Occupations GROUP BY Occupation
) t

Try it here https://dbfiddle.uk/5dNfq0Ch