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?
CodePudding user response:
MySql does not support an unpvot
syntax, not does it support a values
construct, 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