Reformat Department Table - Leetcode Solution

The Reformat Department Table problem on LeetCode involves formatting an input table with three columns, including an ID column, a Name column, and a Salary column, into a new table with two columns, including an ID column and a Salary column. The Name column should be split into first and last name columns, with last names appearing in the ID column when the first name column is non-empty.

The problem can be solved in several steps, as follows:

Step 1: Select the relevant columns from the input table We start by selecting the three columns from the input table using the SELECT statement:

SELECT id, name, salary FROM Employee;

This will return a table with three columns, including the ID column, the Name column, and the Salary column.

Step 2: Split the Name column into first and last name columns Next, we need to split the Name column into first and last name columns using the SUBSTRING_INDEX function. We can split the Name column on the space character, which separates the first and last names. However, if the first name is missing, we need to move the last name to the ID column.

To do this, we can use a CASE statement within the SELECT statement, which checks whether the Name column contains a space character. If it does, we use the SUBSTRING_INDEX function to split the Name column into first and last name columns. Otherwise, we move the entire name to the last name column and set the first name column to NULL:

SELECT id, CASE WHEN INSTR(name, ' ') > 0 THEN SUBSTRING_INDEX(name, ' ', 1) ELSE NULL END AS first_name, CASE WHEN INSTR(name, ' ') > 0 THEN SUBSTRING_INDEX(name, ' ', -1) ELSE name END AS last_name, salary FROM Employee;

This will return a table with four columns, including the ID column, the First_Name column, the Last_Name column and the Salary column.

Step 3: Reformat the table into two columns Finally, we need to reformat the table with two columns, including an ID column and a Salary column. To do this, we can use the SELECT statement again to select the relevant columns from the previous step:

SELECT id, salary FROM ( SELECT id, CASE WHEN INSTR(name, ' ') > 0 THEN SUBSTRING_INDEX(name, ' ', 1) ELSE NULL END AS first_name, CASE WHEN INSTR(name, ' ') > 0 THEN SUBSTRING_INDEX(name, ' ', -1) ELSE name END AS last_name, salary FROM Employee ) AS t;

This will return a table with two columns, including the ID column and the Salary column, which is reformatted from the original table.

1