Split N-Values to N-rows using MySQL Query

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 to 2 Rows only?

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

not clear enough. you need help with a SELECT? Or you want to alter the schema? — delboy1978uk, Sep 14 '17 at 07:57
how many of those comma separated values can appear in the last column? — Ivo P, Sep 14 '17 at 07:59
I upvoted the question because the OP wants to move away from CSV data in his table, which is a step in the right direction and the opposite of what we often see here. — Tim Biegeleisen, Sep 14 '17 at 07:59
@TimBiegeleisen I have used your Query given below, it works fine for splitting only two to Two Rows but i have 25 values in one column ? Is there any dynamic solution for SPLITTING 25 VALUES INTO 25 ROWS...I hope you got my requirement. — Anil Kumar Reddy, Sep 19 '17 at 07:41

score 1 · Accepted Answer · answered Sep 14 '17 at 07:58

1

Here is an option which will work well if every training name entry has two values separated by a single comma.

SELECT
    PARENT_SLNO,
    RNO,
    TRAINING_CATEGORY,
    SUBSTRING_INDEX(TRAINING_NAME, ',', 1) AS TRAINING_NAME
FROM yourTable
UNION ALL
SELECT
    PARENT_SLNO,
    RNO,
    TRAINING_CATEGORY,
    SUBSTRING_INDEX(TRAINING_NAME, ',', -1)
FROM yourTable

If you want to convert your entire table, then you could select the above query into a new table, delete the old one, then rename the new one. If your CSV data could have a varying number of commas then my query would need to be modified, but the general approach could remain the same.

answered Sep 14 '17 at 07:58

Tim Biegeleisen

502,043
27
286
360

Thanks for the reply...it works – Anil Kumar Reddy Sep 14 '17 at 09:25
Totally different question, totally different answer. You should ask a new question in my opinion. – Tim Biegeleisen Sep 14 '17 at 11:37
Continuation to the above Question,I have n values in 'TRAINING_NAME' field, How can i give Return all Rows – Anil Kumar Reddy Sep 14 '17 at 11:56
can you give me dynamic solution for the above query to split N-Values into N-Rows? – Anil Kumar Reddy Sep 19 '17 at 07:44
I think you would need dynamic SQL to handle an arbitrary number of commas. Believe it or not, this is beyond my normal capabilities. – Tim Biegeleisen Sep 19 '17 at 07:52
Can you please check now,i have edited my Question.pls check and give solution if there any possibility? – Anil Kumar Reddy Sep 19 '17 at 08:43
Stack Overflow doesn't work this way. Ask a new question. – Tim Biegeleisen Sep 19 '17 at 08:46

Split N-Values to N-rows using MySQL Query

1 Answers1