1

My dataframe has a column (numbers_selected) and for each row, it looks like this:

1. 6-9-27
2. 2-3-6-8-30
3. 3-11-13-18
4. 3-14-15-17-18-30
5. 3-8-10-12-16
6. 3-7-8-27-29
7. 8-14-21

As you can see each row can have different amount of numbers.

What I am looking for is to see if it is possible to create a column for each of these numbers. There will be up to 6 columns as it is not possible to have more than 6 numbers in a row.

At the end, the dataset should look like these:

1st Number 2nd Number 3rd Number 4th Number 5th Number 6th Number
6 9 27 Null Null Null
2 3 6 8 30 Null
3 11 13 18 Null Null
3 14 15 17 18 30
3 8 10 12 16 Null
3 7 8 27 29 Null
8 14 21 Null Null Null

bar

The query I am using is:

Select Week, number_selected from x.

Hope you can help me with these, thanks!

GMB
  • 216,147
  • 25
  • 84
  • 135
JVA
  • 111
  • 4
  • 10

1 Answers1

0

How about a json approach?

select week,
    js ->> '$[0]' as num1,
    js ->> '$[1]' as num2,
    js ->> '$[2]' as num3,
    js ->> '$[3]' as num4,
    js ->> '$[4]' as num5,
    js ->> '$[5]' as num6
from (
    select t.*, concat('[', replace(numbers_selected, '-', ','), ']') as js
    from mytable t
) t

This works by using string functions to convert the dash-separated string to something that looks like a json array. Typically, '6-9-27' becomes [6,9,27]. We can then use json accessor ->> to bring each array element by index.

Demo on DB Fiddle:

week | num1 | num2 | num3 | num4 | num5 | num6
---: | :--- | :--- | :--- | :--- | :--- | :---
   1 | 6    | 9    | 27   | null | null | null
   2 | 2    | 3    | 6    | 8    | 30   | null
   3 | 3    | 11   | 13   | 18   | null | null
   4 | 3    | 14   | 15   | 17   | 18   | 30  
   5 | 3    | 8    | 10   | 12   | 16   | null
   6 | 3    | 7    | 8    | 27   | 29   | null
   7 | 8    | 14   | 21   | null | null | null
GMB
  • 216,147
  • 25
  • 84
  • 135
  • and any way of doing it directly in MySQL? I like your approach but the table has like 500 thousand rows, so it would be very manual to insert all these rows when creating the table – JVA Dec 14 '20 at 23:35
  • @JVA: I don't understand your question. This is a pure MySQL query. – GMB Dec 14 '20 at 23:40
  • @JVA do you mean you wanted to actually add new columns and set them rather than just returning the split data in a query? – ysth Dec 14 '20 at 23:58
  • pre-5.7, without using json: https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=b838437685cbd2270debe1810cf7038f – ysth Dec 14 '20 at 23:59
  • @JVA: which version of MySQL are you running? `select version()` can tell you. – GMB Dec 15 '20 at 10:03
  • @ysth: I reopened the question. You might want to post your suggestion as an answer. – GMB Dec 15 '20 at 10:04