mysql - split columns to rows dynamically

Question

I have the table training, I want to split Training_name Column values to multiple Rows:

SLNO Category Training_name
1     A        1,5,9,15,12,16
2     B        2,6,10,17
3     C        1,3,7,19,14,18

I used below Query but using this Query i can only split into two rows...

SELECT training.SLNO,training.CATEGORY, SubString_Index(training.TRAINING_NAME, ',', 1) AS TRAINING_NAME FROM training UNION ALL SELECT training.SLNO,training.CATEGORY, SubString_Index(training.TRAINING_NAME, ',', -1) FROM training

i am trying to get the table as given below,Please help me out

SLNO Category Training_name
1      A        1
1      A        5
1      A        9
1      A        15
1      A        12
1      A        16
2      B        2
2      B        6
2      B        10
2      B        17
3      C        1
3      C        3
3      C        7
3      C        19
3      C        14
3      C        18

This will probably be a one time operation to normalize your data, so a rough bit of code will do. You should try to do this in PHP. SO is not a code writing service so you should at least show some attempt at this. — KIKO Software, Sep 19 '17 at 12:26
https://stackoverflow.com/questions/17308669/reverse-group-concat-in-mysql — etsa, Sep 19 '17 at 12:28
@etsa: As I tried to say: By using PHP you can simplify this enormously. — KIKO Software, Sep 19 '17 at 12:29
Like this in the question asked in https://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows ?? — Anshuman Mitra, Sep 19 '17 at 12:33

score 3 · Answer 1 · answered Sep 19 '17 at 12:43

DROP TABLE IF EXISTS my_bad_table;
DROP TABLE IF EXISTS my_good_table;

CREATE TABLE my_bad_table
(SLNO INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,Category CHAR(1) NOT NULL
,Training_name VARCHAR(200) NOT NULL
);

INSERT INTO my_bad_table VALUES
(1,'A','1,5,9,15,12,16'),
(2,'B','2,6,10,17'),
(3,'C','1,3,7,19,14,18');

CREATE TABLE my_good_table AS
SELECT DISTINCT x.SLNO
              , x.Category
              , CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(x.training_name,',',y.i+1),',',-1) AS UNSIGNED) training_name
           FROM my_bad_table x
              , (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 
                 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 8 UNION SELECT 9) y
          ORDER 
             BY slno
              , category
              , training_name;

SELECT * FROM my_good_table;
+------+----------+---------------+
| SLNO | Category | training_name |
+------+----------+---------------+
|    1 | A        |             1 |
|    1 | A        |             5 |
|    1 | A        |             9 |
|    1 | A        |            12 |
|    1 | A        |            15 |
|    1 | A        |            16 |
|    2 | B        |             2 |
|    2 | B        |             6 |
|    2 | B        |            10 |
|    2 | B        |            17 |
|    3 | C        |             1 |
|    3 | C        |             3 |
|    3 | C        |             7 |
|    3 | C        |            14 |
|    3 | C        |            18 |
|    3 | C        |            19 |
+------+----------+---------------+

If 1s are always 'A', etc, then a further step towards normalization is required to remove that redundancy.

score 1 · Answer 2 · answered Sep 19 '17 at 12:29

Here is one method:

select slno, category, substring_index(training_name, ',', 1) + 0 as training_id
from t
union all
select slno, category, substring_index(substring_index(training_name, ',', 2), ',', -1) + 0 as training_id
from t
where training_name like '%,%'
union all
select slno, category, substring_index(substring_index(training_name, ',', 3), ',', -1) + 0 as training_id
from t
where training_name like concat('%', repeat(',%', 2))
union all
select slno, category, substring_index(substring_index(training_name, ',', 4), ',', -1) + 0 as training_id
from t
where training_name like concat('%', repeat(',%', 3))
union all
 . . .

Repeat for as often as you need. Store the results in a new table. Fix foreign key references and other aspects of the data. Drop the original table (well, archive it) and never use that structure again.

score 0 · Answer 3 · answered Sep 20 '17 at 04:24

It works to me..

SELECT DISTINCT x.PARENT_SLNO, x.TRAINING_CATEGORY, CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(x.TRAINING_NAME,',',y.i+1),',',-1) AS UNSIGNED) TRAINING_NAME FROM assessment_training x, (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 8 UNION SELECT 9) y where PARENT_SLNO = 3 and TRAINING_CATEGORY='technical' ORDER BY PARENT_SLNO,TRAINING_CATEGORY,TRAINING_NAME

mysql - split columns to rows dynamically

3 Answers3