[MySQL] creating new column based off categorical data in two other columns, duplicating for each value
hey folks --
apologies for the title gore. I can't actually think of the cleanest way to ask this question (which is partially why I haven't had luck using Claude/etc.). I'll try to explain with a relatively simple example.
let's say you've got columns A, B, and C.
A is a user id (can be treated as a string). B and C are categorical columns (string values) -- let's say B is 'cuisine' and C is 'country'.
presently my table is keyed on A -- there's only a single row, and that row has columns for both cuisine and country.
I'm trying to find the most efficient way to create a new table that has two columns: A and B_C.
for every value in A, there needs to be two rows where one row's B_C value is the value that was in 'cuisine' and the other row's B_C value is what was in 'country.'
I know that I can, e.g., query the original table twice and do a 'CASE WHEN' -- I'm trying to figure out if there's a way to do this in a single query.