SQL to turn multivalue fields into multiple rows
Posted: Fri Aug 07, 2009 11:26 pm
Hi all,
In one of my jobs i have a logic to be achieved which is explained below
I/p
COLA COLB COLC COLD COLE COLF
1 A1 B1 C3 D5 E7
2 A11 C12 F4 G34 H54;A12;C13
3 V1 X3 G2 S12 A12;E2;D67
i.e there might be multiple values present in the data which are seperated by special character ' ; '.I need to split these multivalues into seperate records i.e the required o/p is
O/p
COLA COLB COLC COLD COLE COLF
1 A1 B1 C3 D5 E7
2 A11 C12 F4 G34 H54
2 A11 C12 F4 G34 A12
2 A11 C12 F4 G34 C13
3 V1 X3 G2 S12 A12
3 V1 X3 G2 S12 E2
3 V1 X3 G2 S12 D67
Please help me in achieving this logic.
Regards,
Balakrishna
*Note: Title edited to be more descriptive - Content Editor*
In one of my jobs i have a logic to be achieved which is explained below
I/p
COLA COLB COLC COLD COLE COLF
1 A1 B1 C3 D5 E7
2 A11 C12 F4 G34 H54;A12;C13
3 V1 X3 G2 S12 A12;E2;D67
i.e there might be multiple values present in the data which are seperated by special character ' ; '.I need to split these multivalues into seperate records i.e the required o/p is
O/p
COLA COLB COLC COLD COLE COLF
1 A1 B1 C3 D5 E7
2 A11 C12 F4 G34 H54
2 A11 C12 F4 G34 A12
2 A11 C12 F4 G34 C13
3 V1 X3 G2 S12 A12
3 V1 X3 G2 S12 E2
3 V1 X3 G2 S12 D67
Please help me in achieving this logic.
Regards,
Balakrishna
*Note: Title edited to be more descriptive - Content Editor*