0

I have two tables A: 

COL1 | COL2 | IDS
10   | 2    | 1,2,4
20   | 3    | 1,3
30   | 1    | 1,3,4,6

and B:

ID | NAME
1  | XYZ
2  | ABC
3  | PQR
4  | STV
5  | LMN
6  | IJK

now I have to split rows in table A depending on values inside column IDS. IDS is a column having multiple IDs (present in table B) separated by coma and number of such IDs in a column IDS is not fixed.

after splitting operation each row in A should split into x number of new rows (where x is the number of IDs present in column IDS) and each new row will have same values for COL1 & COL2 for the same row in table A.

the resulting table should look like this:

COL1 | COL2 | ID
10   | 2    | 1
10   | 2    | 2
10   | 2    | 4
20   | 3    | 1
20   | 3    | 3
30   | 1    | 1
30   | 1    | 3
30   | 1    | 4
30   | 1    | 6

What is the best way to achieve this row transformation?

MikA
  • 5,184
  • 5
  • 33
  • 42
  • Which database do you want to use? E.g. Postgres can `unnest` arrays like here http://stackoverflow.com/questions/8760419/postgresql-unnest-with-element-number – contradictioned Jul 20 '13 at 19:19
  • Depending on the RDBMS, this is possibly most easily done by loading your rows in code and re-inserting them. The RDBMS itself may not have native support for this operation, and it's a good thing you are taking the step to properly normalize the table. – Michael Berkowski Jul 20 '13 at 19:20

0 Answers0