Pivoting a table of unique column values based on column string?

December 15, 2021

Not sure how else to phrase the post title, but I have a table of data like:

ID1   F1   F2   F3
X1 Enabled Disabled Disabled
X2 Disabled Enabled Enabled

I’d like to get it into the form of:

ID1  Fields
X1  F1
X2  F2
X2  F3

Originally I had a form of something like

SELECT ID1, CONCAT_WS(',', IF(F1='Enabled','F1',NULL), IF(F2='Enabled','F2',NULL), ...)

But I found out that mysql doesn’t have a string_split() function natively built in to break up the concatenated stuff into an array and just copy the ID1 field downwards.

I’ve tried using a CASE WHEN here, but it doesn’t seem appropriate since I don’t want to overwrite the value based on a match, I just want all of the fields. I feel like I’m missing a relatively simple operation that’s right in front of me. Any suggestions?

>Solution :

MySql does not support an unpvot syntax, not does it support a values contruct, which only really leaves union:

select id1, fields from (
    select id1, case when f1='enabled' then 'f1' end as Fields from t
    union all
    select id1, case when f2='enabled' then 'f2' end from t
    union all
    select id1, case when f3='enabled' then 'f3' end from t
)u
where fields is not null