1

I have a large dataset that contains one line per person, and 25 criteria columns that may or may not be populated, like this sample:

SELECT * FROM Criteria_Input; ╔══════════╦═══════════╦═══════════╦═══════════╦═══════════╦═══════════╦═══════════╗ ║ Name ║ Criteria1 ║ Criteria2 ║ Criteria3 ║ Criteria4 ║ Criteria5 ║ Criteria6 ║ ╠══════════╬═══════════╬═══════════╬═══════════╬═══════════╬═══════════╬═══════════╣ ║ Michael ║ ║ Yes ║ ║ ║ Yes ║ ║ ║ Brant ║ ║ ║ ║ ║ ║ Yes ║ ║ Mary ║ Yes ║ ║ ║ Yes ║ ║ ║ ║ John ║ ║ ║ ║ ║ Yes ║ ║ ║ Connie ║ ║ ║ Yes ║ Yes ║ ║ ║ ╚══════════╩═══════════╩═══════════╩═══════════╩═══════════╩═══════════╩═══════════╝

I need to convert this data into rows like this:

SELECT * FROM Criteria_Final; ╔═════════╦═══════════╗ ║ Name ║ Criteria ║ ╠═════════╬═══════════╣ ║ Michael ║ Criteria2 ║ ║ Michael ║ Criteria5 ║ ║ Brant ║ Criteria6 ║ ║ Mary ║ Criteria1 ║ ║ Mary ║ Criteria4 ║ ║ John ║ Criteria5 ║ ║ Connie ║ Criteria3 ║ ║ Connie ║ Criteria4 ║ ╚═════════╩═══════════╝

or even better, something this:

SELECT * FROM Criteria_Final; ╔═════════╦══════════╗ ║ Name ║ Criteria ║ ╠═════════╬══════════╣ ║ Michael ║ 2 ║ ║ Michael ║ 5 ║ ║ Brant ║ 6 ║ ║ Mary ║ 1 ║ ║ Mary ║ 4 ║ ║ John ║ 5 ║ ║ Connie ║ 3 ║ ║ Connie ║ 4 ║ ╚═════════╩══════════╝

Is this kind of data transformation possible in MySQL? The reason I need to do this kind of conversion is that I will join the resulting table with another table on the criteria number.

Improve this question
1

2 Answers 2

Reset to default
3

Try query with UNIONs -

SELECT * FROM ( SELECT name, 1 AS criteria FROM cr WHERE LENGTH(Criteria1) > 0 UNION SELECT name, 2 AS criteria FROM cr WHERE LENGTH(Criteria2) > 0 UNION SELECT name, 3 AS criteria FROM cr WHERE LENGTH(Criteria3) > 0 UNION SELECT name, 4 AS criteria FROM cr WHERE LENGTH(Criteria4) > 0 UNION SELECT name, 5 AS criteria FROM cr WHERE LENGTH(Criteria5) > 0 UNION SELECT name, 6 AS criteria FROM cr WHERE LENGTH(Criteria6) > 0 ) t ORDER BY name, criteria

Change WHERE condition to check NULL values if needed.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you @Devart, big time; this is an elegant solution and I love it! I put my final working code below.
1

Here is my solution, based upon the beautiful solution from @Devart. First, the creation of the input table:

CREATE TABLE Criteria_Input ( Name VARCHAR(10), Criteria1 VARCHAR(5), Criteria2 VARCHAR(5), Criteria3 VARCHAR(5), Criteria4 VARCHAR(5), Criteria5 VARCHAR(5), Criteria6 VARCHAR(5), Criteria7 VARCHAR(5), Criteria8 VARCHAR(5) );

Next, the creation of the final table:

CREATE TABLE Criteria_Final ( Name VARCHAR(10), Criteria INT(2) );

Then the insertion of the sample data:

INSERT INTO Criteria_Input (`Name`, `Criteria1`, `Criteria2`, `Criteria3`, `Criteria4`, `Criteria5`, `Criteria6`) VALUES ('"Michael"', NULL, '"Yes"', NULL, NULL, '"Yes"', NULL), ('"Brant"', NULL, NULL, NULL, NULL, NULL, '"Yes"'), ('"Mary"', '"Yes"', NULL, NULL, '"Yes"', NULL, NULL), ('"John"', NULL, NULL, NULL, NULL, '"Yes"', NULL), ('"Connie"', NULL, NULL, '"Yes"', '"Yes"', NULL, NULL) ;

And finally, the code that converts the data:

INSERT INTO Criteria_Final SELECT * FROM ( SELECT name, 1 AS Criteria FROM Criteria_Input WHERE Criteria1 IS NOT NULL UNION SELECT name, 2 AS Criteria FROM Criteria_Input WHERE Criteria2 IS NOT NULL UNION SELECT name, 3 AS Criteria FROM Criteria_Input WHERE Criteria3 IS NOT NULL UNION SELECT name, 4 AS Criteria FROM Criteria_Input WHERE Criteria4 IS NOT NULL UNION SELECT name, 5 AS Criteria FROM Criteria_Input WHERE Criteria5 IS NOT NULL UNION SELECT name, 6 AS Criteria FROM Criteria_Input WHERE Criteria6 IS NOT NULL ) Criteria_Input ORDER BY Name, Criteria;

You can see this in operation on SQL Fiddle.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.