Return SET from single result as multiple rows

Question

I have a MySQL 5.5 table with a column containing a SET datatype. Each entry has between 0 to 4 values for the SET column. I would like to write a SELECT statement that will return a separate row for each value in the SET. Is this possible with a single statement?

Thanks in advance, Peter

EDIT

Edited to include an example:

The table structure is very basic:

INT(6) UNSIGNED PRIMARY KEY (id)
VARCHAR(20) UNIQUE INDEX (material)
SET('Solid','Liquid','Gas','Plasma') (states)

In the example above you can imagine having a table populated with various materials (material) and the SET column for each includes all of the states (states) that that material can exist in. I'd like to see the statement return a seperate row for each SET value that a material has, duplicating the id and material name.

Hope that helps clarify things.

Would the `SELECT ... WHERE set_col & 1 UNION ALL SELECT ... WHERE set_col & 2`, etc. do the trick for you? — raina77ow, Jul 07 '12 at 23:13
There are more than 20 options in the SET, which makes this solution a bit tedious to implement. If there simply isn't a better way to do it I'll fall back to this method. — Peter Hanneman, Jul 07 '12 at 23:23
@PeterHanneman, can you post your table schema as well as some sample data so that we're more clear on what we're working with here? — Zane Bien, Jul 07 '12 at 23:33
Probably best to restructure your `SET` column into a relationship table. — eggyal, Jul 07 '12 at 23:45
@eggyal - I'm migrating the database from an old table structure into a new schema. ;-) — Peter Hanneman, Jul 08 '12 at 00:02
@ZaneBien - I've added an example to help clarify what I'm after. — Peter Hanneman, Jul 08 '12 at 00:07
possible duplicate of [MYSQL - Split Data Into Multiple Rows](http://stackoverflow.com/q/5342629/). See also [How can I represent a single row from result set as multiple rows?](http://stackoverflow.com/a/2154433/), [Transpose a row into columns with MySQL without using UNIONS?](http://stackoverflow.com/q/1851781/), [Can you split/explode a field in a MySQL query?](http://stackoverflow.com/q/471914/). — outis, Jul 08 '12 at 00:43

Zane Bien · Accepted Answer · 2012-07-08T01:10:56.120

3

If there are 20 possible states, but a material can only have up to 4:

Try this solution:

SELECT 
    a.material, 
    a.states
FROM
(
    SELECT material, SUBSTRING_INDEX(states,',',1) AS states
    FROM settbl

    UNION

    SELECT material, SUBSTRING_INDEX(SUBSTRING_INDEX(states,',',-3),',',1) AS states
    FROM settbl

    UNION

    SELECT material, SUBSTRING_INDEX(SUBSTRING_INDEX(states,',',-2),',',1) AS states
    FROM settbl

    UNION

    SELECT material, SUBSTRING_INDEX(states,',',-1) AS states
    FROM settbl
) a
ORDER BY
    a.material

SQL-Fiddle Demo

edited Jul 08 '12 at 01:10

answered Jul 08 '12 at 00:43

Zane Bien

22,685
6
45
57

Actually, what I had was slightly wrong (wasn't selecting the second item in the list), use the edited solution I just made. – Zane Bien Jul 08 '12 at 01:00

Return SET from single result as multiple rows

1 Answers1