MySQL split up multiple columns into multiple rows

Question

I have a customer supplied dataset which looks like this: (simplified)

|ForiegnKey|Code1|Code2|Code3|  
|==========|=====|=====|=====|  
|A         |10   |20   |30   |  
|A         |10   |20   |30   |  
|B         |100  |200  |     |  
|C         |25   |35   |40   |  
|D         |1000 |     |     | 
|E         |     |     |9999 |

For the final product, I need this entered in a new table 1 code at a time, so like so:

|ForiegnKey|Sequence|Code|
|A         |1       |10  |
|A         |2       |20  |
|A         |3       |30  |
|B         |1       |100 |
|C         |1       |25  |
|C         |2       |35  |
|C         |3       |40  |
|D         |1       |1000|
|E         |1       |9999|

For my question, is there a simple way to do this one in query that doesn't involve using unions?
I have to generate the sequence number based on the Value of the ForiegnKey (i.e. in the Case of ForiegnKey E, even tho it was in Column Code3, I need it to have a Sequence of 1.) My current thought path is that if I can get this out of the table without using a Union, I could generate the Sequence ID in the same statement.
Empty code values such as D-2 and 3, and E-1 and 2 should be skipped.
I have started working on this using a temporary table but thought I would check here and see if there was an easier way.

Thanks

I know you don't want to use union queries, but could you achieve what you are looking for by putting union queries in a sub select and using that in your FROM clause? — Tom
– Tom, Commented Sep 17, 2012 at 19:17
You may write a stored procedure which will iterate over the table line by line, inspecting the code columns and inserting records into a new table. — Michael
– Michael, Commented Sep 17, 2012 at 19:50

Alain Collins · Accepted Answer · 2012-09-17 20:16:12Z

3

You sound more interested in getting the results in one batch than in avoiding UNIONS. Here you go:

set @last_key = null;
set @seq = 1;
select foreignkey, case when @last_key is null or @last_key != foreignkey then @seq := 1 else @seq := @seq + 1 end as sequence, code, @last_key := foreignkey as foo  from (
  select foreignkey, code1 as code
  from dataset
  where code1 is not null
  union
  select foreignkey, code2
  from dataset
  where code2 is not null
  union
  select foreignkey, code3
  from dataset
  where code3 is not null
  order by 1
) foo;

answered Sep 17, 2012 at 20:16

Alain Collins

16.4k2 gold badges34 silver badges56 bronze badges

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

Collectives™ on Stack Overflow

MySQL split up multiple columns into multiple rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related