How to Unpivot the data from a single row into multiple rows in MySQL

Question

1. Requirement 1:
   • TO UNPIVOT the given data (as shown below) in a row into multiple rows (should exclude the null values) as shown in Output required.
   • The GRADES column can have multiple values (the code has to loop until the complete set is split or unpivoted.

• Source:

ID	GRADES
123	[A,B,C,C,D]
456	[A,,D]

• Output required:

ID	GRADES
123	A
123	B
123	C
123	C
123	D
456	A
456	D

2. Requirement 2:
   • Use the unpivoted data (as shown below) & select only the distinct records from them and pivot them back to load into a different table. as shown in Output required.

• Source:

ID	GRADES
123	A
123	B
123	C
123	C
123	D
456	A
456	D

• Output required:

ID	GRADES
123	[A,B,C,D]
456	[A,D]

I tried to create a procedure using substring, locate, instr to split the values and concat them back but unable to achieve the required output. Currently stuck with the logic.

Answer 1

For Requirement 1:

-- Assuming you have a table called 'source' with columns 'ID' and 'GRADES'

-- Create a temporary table to hold the unpivoted data
CREATE TEMPORARY TABLE temp_unpivoted_data (
  ID INT,
  GRADE CHAR(1)
);

-- Unpivot the data from the 'source' table
INSERT INTO temp_unpivoted_data (ID, GRADE)
SELECT ID, SUBSTRING_INDEX(SUBSTRING_INDEX(GRADES, ',', n), ',', -1) AS GRADE
FROM source
JOIN (
  SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 -- Add more if needed
) AS numbers
ON CHAR_LENGTH(GRADES) - CHAR_LENGTH(REPLACE(GRADES, ',', '')) >= n - 1
WHERE GRADES <> '';

-- Retrieve the unpivoted data
SELECT ID, GRADE
FROM temp_unpivoted_data;

For Requirement 2:

-- Create a temporary table to hold the distinct pivoted data
CREATE TEMPORARY TABLE temp_pivoted_data (
  ID INT,
  GRADES VARCHAR(100)
);

-- Pivot the data from the 'temp_unpivoted_data' table
INSERT INTO temp_pivoted_data (ID, GRADES)
SELECT ID, GROUP_CONCAT(DISTINCT GRADE ORDER BY GRADE SEPARATOR ',') AS GRADES
FROM temp_unpivoted_data
GROUP BY ID;

-- Retrieve the pivoted data
SELECT ID, GRADES
FROM temp_pivoted_data;