Get normalized representation of SET datatype In MySQL you can SELECT SET values from a table using this syntax:

CREATE TABLE `test_set` (
  `int` int(11) DEFAULT NULL,
  `set_2` set('a','b','c') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

SELECT * FROM test_set WHERE set_2 &amp; 1;


or equivalent using = operator.

For a given table:

int | set_2
-----------
12  | a,b
-----------
34  | c,b


I'd like to make a query that would return:

int | set_2
-----------
12  | a
-----------
12  | b
-----------
34  | c
-----------
34  | b


How would such query look in mysql/mariadb? According to the book I am reading, it should be something in the lines of:

SELECT T.int, S as set_value
FROM test_set T, T.set_2 as S 


Similarly, set_gen() function can be applied to reverse normalized representation to the one containing sets:

SELECT T.int, set_gen(T.set_2) 
FROM test_set T
GROUP BY T.int


What is the syntax for denormalization in mysql/mariadb?
SELECT DISTINCT `int`, SUBSTRING_INDEX(SUBSTRING_INDEX(`set_2`, ',', `mask`), ',', -1) `set`
FROM `test_set`, 
      -- generate masks based on SET values list count
     (SELECT 1 `mask` UNION 
      SELECT 2 UNION 
      SELECT 3) `mask`
WHERE `set_2` &amp; 1;


fiddle

If server version allows build mask in recursive CTE (dynamic count may be used)