MySQL - How to remove space between Name and Occupation in CASE statement-CodePudding

I'm looking to remove the space between Name and Occupation. Below is my SQL code snippet with the output versus the output I want. Let me know if you have any idea or suggestions.

MY CODE:

SELECT Name,
CASE
   WHEN Occupation='Actor' THEN '(A)'
   WHEN Occupation='Doctor' THEN '(D)'
   WHEN Occupation='Professor' THEN '(P)'
   ELSE '(S)'
END AS Test
FROM Occupations ORDER BY Name ASC;

MY OUTPUT:

Aamina (D)
Ashley (P)
Belvet (P)
Britney (P)
Christeen (S)
Eve (A)
Jane (S)
Jennifer (A)
Jenny (S)
Julia (D)
Ketty (A)
Kristeen (S)
Maria (P)
Meera (P)
Naomi (P)
Priya (D)
Priyanka (P)
Samantha (A)

THE OUTPUT I NEED (NO SPACES):

Aamina(D)
Ashley(P)
Belvet(P)
Britney(P)
Christeen(S)
Eve(A)
Jane(S)
Jennifer(A)
Jenny(S)
Julia(D)
Ketty(A)
Kristeen(S)
Maria(P)
Meera(P)
Naomi(P)
Priya(D)
Priyanka(P)
Samantha(A)

CodePudding user response：

Your current query is treating "Name" and "Occupation" as 2 columns. You'll need to use concatenate to mash them together.

    SELECT concat(Name,
CASE
   WHEN Occupation='Actor' THEN '(A)'
   WHEN Occupation='Doctor' THEN '(D)'
   WHEN Occupation='Professor' THEN '(P)'
   ELSE '(S)')
END AS Test
FROM Occupations ORDER BY Name ASC;

CodePudding user response：

Instead of listing 2 columns (comma separated), just concatenate the two string expressions:

SELECT
  CONCAT(
    fname 
  , CASE
      WHEN Occupation='Actor' THEN '(A)'
      WHEN Occupation='Doctor' THEN '(D)'
      WHEN Occupation='Professor' THEN '(P)'
    ELSE '(S)'
  END 
  ) AS Test
FROM occupations
ORDER BY test
;