- 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.
ID |
GRADES |
123 |
[A,B,C,C,D] |
456 |
[A,,D] |
ID |
GRADES |
123 |
A |
123 |
B |
123 |
C |
123 |
C |
123 |
D |
456 |
A |
456 |
D |
- 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.
ID |
GRADES |
123 |
A |
123 |
B |
123 |
C |
123 |
C |
123 |
D |
456 |
A |
456 |
D |
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.
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;