Mysql - Merge multiple columns into one while preserving each value

Question

Is there a simple way in Mysql to convert a multi-column resultset into a single-column resultset where each row in the single column contains a single value from each cell in the multi-column resultset?

For instance, say I have a table like:

    id    |   fk1   |   fk2   |   fk3   
    1          2         3         4
    5          6         7         8

Ideally I'd like to be able to run a query along the lines of:

SELECT <some_function>(fk1, fk2, fk3) AS value FROM myTable;

...and then get an output like:

Is there a straightforward way of doing so, or is the only real option to walk the multi-column resultset in code and extract the values into the format that I want?

The end goal is to be able to use the first query as a subquery in a context where the input can only be a single column of values, like:

SELECT * FROM myOtherTable WHERE id IN 
    (SELECT <some_function>(fk1, fk2, fk3) AS value FROM myTable);

Thus a pure SQL solution is preferable, if one exists.

Gordon Linoff · Accepted Answer · 2015-02-25 12:13:10Z

3

The simplest way is to use union all:

select fk1 from mytable union all
select fk2 from mytable union all
select fk3 from mytable;

There are other methods, but this is simplest.

For your particular query, you can use exists and or or in:

SELECT mo.*
FROM myOtherTable mo
WHERE EXISTS (SELECT 1
              FROM MyTable m
              WHERE mo.id IN (m.fk1, m.fk2, m.fk3)
             );

EDIT:

For performance, but an index on each fk column and use multiple conditions in the where:

SELECT mo.*
FROM myOtherTable mo
WHERE EXISTS (SELECT 1 FROM MyTable m WHERE mo.id = m.fk1) OR
      EXISTS (SELECT 1 FROM MyTable m WHERE mo.id = m.fk2) OR
      EXISTS (SELECT 1 FROM MyTable m WHERE mo.id = m.fk3);

This will do index lookups, which should be much faster than the original form.

edited Feb 25, 2015 at 12:13

answered Feb 25, 2015 at 2:45

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

aroth Over a year ago

Thanks, the where exists method works (haven't tried the union all as the query on myTable actually has its own subquery which I don't want to repeat once for each field), but takes significantly longer than just querying for and iterating the multi-column resultset. Any ideas on things to try to speed it up?

Gordon Linoff Over a year ago

@aroth . . . Speeding it up is an entirely different matter. For that, I would suggest normalizing the data structure so you have only one fk per row.

aroth Over a year ago

I was afraid of that. Unfortunately there's quite a bit of legacy code in many different places that's built around the separate fields. The query is quite fast if I just select into multiple columns, so I guess I'll just do that and handle the rest programmatically.

Bipul Chandra Dev Nath · Accepted Answer · 2017-12-11 08:15:11Z

0

Consider you table name tab1.

Now you can do query like

select fk1 from (
   (select fk1 from tab1) 
      union all 
   (select fk2 from tab1)) as rt 
      group by fk1;

This will combine your two column in to one. Contains will appear one after another.

answered Dec 11, 2017 at 8:15

Bipul Chandra Dev Nath

4314 silver badges15 bronze badges

Collectives™ on Stack Overflow

Mysql - Merge multiple columns into one while preserving each value

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related