1

I have the following table (named table A):

 id  | col A (varchar)| col B (varchar)
  1  | foo            | 1,2,3,...
  2  | bar            | 4,5,6,...
 ... | ...            | ...

I want to transform it to the following in the same table A:

 id  | col A (varchar)| col B (int)
  3  | foo            | 1
  4  | foo            | 2
  5  | foo            | 3
  6  | foo            | ...
  7  | bar            | 4
  8  | bar            | 5
  9  | bar            | 6
  10 | bar            | ...
 ... | ...            | ...

Do I have sql script way to do that?

yanivsh
  • 293
  • 1
  • 5
  • 16
  • Will `col B` always be three values, or can there be more? Can there be less? – toonice May 01 '17 at 06:17
  • It can be more than 3 values and it can be less as well – yanivsh May 01 '17 at 06:19
  • Are you asking us to change the type of `col B` to `INT` as well as break up the values? Are you asking us to leave the type as `VARCHAR` and put the individual values in `col B`? Do you wish to keep the old rows and add the new ones, or replace the old rows with the new ones? – toonice May 01 '17 at 06:22
  • Must the values going into `col B` be unique for each value in `col A` or are duplication okay? Can the first `table A` have more than one record with a `col A` value of `foo`? – toonice May 01 '17 at 06:24
  • @toonice Yes. I want to change Col B to INT as well as break up the values. Eventually I will want to delete the old rows with the comma separated string. Duplication in Col B are OK. and table A can have more than 1 rows with the values of foo. There are no restrictions beside the id to be unique – yanivsh May 01 '17 at 06:27
  • 1
    @trincot - though, you posted possible duplicate of `sql server` version, when this post tagged as `mysql` – Oto Shavadze May 01 '17 at 06:31
  • 1
    Duplicates for MySql: [here](http://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows), and [here](http://stackoverflow.com/questions/18304857/split-delimited-string-value-into-rows). – trincot May 01 '17 at 06:33

0 Answers0