Creating new data table on existing one

Question

Hello I've got a question, how (if it possible), I can create new datatables with close same rows but if In column value is in string "/" for example

ID	column_param	column_sym	column_value	column_val2
First	param_test1	ABC	11/12	test
Second	param_test2	CDE	22/11	test
Third	param_test3	EFG	44	teste
4'th	param_test4	HIJ	33/22	test

And here if I have param_test1 and param_test4 and if in this column value has "/" I want to create 2 other rows but if I will not set param_test2 then it stay as it is and everything should be in new datatable. Is any way to create this? Thank you in advance.

Expected result:

Very much lost in translation..can you add your expected outcome please, — P.Salmon, Feb 12 '21 at 10:47
Is it possible that `param_test1` or `param_test4` row have according `column_value` (1) without `/`? (2) with more than one `/`? (3) with equal parts (for example, `'12/12'`)? — Akina, Feb 12 '21 at 10:57
Yes it is possible, not every param_test1 has "/" and sometimes there is more than (1) "/" — Kamil, Feb 12 '21 at 10:59

score 0 · Answer 1 · answered Feb 12 '21 at 11:44

I don't know how the id is being set, but you can do what you want using union all:

select column_param, column_sym,
       substring_index(column_value, '/', 1) as column_value,
       column_val2
from t
union all
select column_param, column_sym,
       substring_index(column_value, '/', -1) as column_value,
       column_val2
from t
where column_value = '%/%';

Steve Lovell · Accepted Answer · 2021-02-12T12:11:21.490

0

As per Gordon's answer, I'm not sure what should be done with the your ID column. I've replaced these with row numbers.
Depending on your version of MySQL/MariaDB, the ROW_NUMBER() window function may not be available. Depending on whether IDs in the output are necessary you may be able to simply omit this.
I've assumed the existence of a table called myNumbers which contains a single field num and is populated with positive integers from 1 to whatever you're likely to need.

I've included more in the output that you asked for, which will hopefully help you understand what's going on

SELECT
    ROW_NUMBER() OVER (ORDER BY d.ID, n.num) as NewID,
    d.ID as OriginalID,
    n.num as,
    d.column_param,
    d.column_sym,
    d.column_value as orig_value,
    CASE WHEN column_param = 'param_test2' THEN d.column_value
    ELSE substring_index(substring_index(d.column_value,'/',n.num),'/',-1) END as split_value,
    d.column_val2
FROM
    myData d
    JOIN myNumbers n on char_length(d.column_value)-char_length(replace(d.column_value,'/','')) >= n.num-1
WHERE
    n.num = 1 OR d.column_param <> 'param_test2'
ORDER BY
    d.ID,
    n.num

See this DB Fiddle (the columns output in a different order than I've specified, but I think that's a DB Fiddle quirk).

If you only want to "split" say param_test1 and param_test4 rows the code above code could be amended as follows:

SELECT
    ROW_NUMBER() OVER (ORDER BY d.ID, n.num) as NewID,
    d.ID as OriginalID,
    d.column_param,
    d.column_sym,
    n.num,
    d.column_value as orig_value,
    CASE WHEN column_param NOT IN ('param_test1','param_test4') THEN d.column_value
    ELSE substring_index(substring_index(d.column_value,'/',n.num),'/',-1) END as split_value,
    d.column_val2
FROM
    myData d
    JOIN myNumbers n on char_length(d.column_value)-char_length(replace(d.column_value,'/','')) >= n.num-1
WHERE
    n.num = 1 OR d.column_param  IN ('param_test1','param_test4')
ORDER BY
    d.ID,
    n.num

edited Feb 12 '21 at 12:11

answered Feb 12 '21 at 11:57

Steve Lovell

2,564
2
13
16

ID is just a number, I'll not do anythink with ID. And I'll check your answer :) – Kamil Feb 12 '21 at 11:59
1 more question is it possible to change "param_test2" to only "param_test1 and param_test4" and inverse this query a little bit because params like "param_test1" I've got a few, but params like "param_test2" I've got many thousands, and I want to only divide rows with params like "param_test1" or "param_test4" – Kamil Feb 12 '21 at 12:05
Yes, if you have only a few different `column_param` values which need splitting, and many that don't, that would make sense. I'll post a tweaked version shortly. – Steve Lovell Feb 12 '21 at 12:07
Nice, thank you very much I'll test that. I hope it will work :) It would save my life :D – Kamil Feb 12 '21 at 12:15
If it works, then come back and upvote and accept the answer to help others find useful stuff. If it doesn't work, then comment accordingly and we'll see if we can fix it. – Steve Lovell Feb 12 '21 at 12:16
Okej I'll, but one more question I've got info (error) that "myNumbers" table doesn't exist, I change everything as should it be but only this one left, I thought this is virtual table, what should I change in this? I also deleted two rows under select because I'll not use that – Kamil Feb 12 '21 at 12:31
A "numbers" table can be quite useful and may be worth creating. You can see how in my DB Fiddle. If you don't want to do that, you could create it as a temporary table (https://stackoverflow.com/questions/38044911/how-can-i-create-a-temporary-table-in-mariadb-or-mysql). Such tables only exist while you run the code. – Steve Lovell Feb 12 '21 at 12:35
Okej, I can create then datatable like "numbers" but Have I add any numbers to that? I've got huge DB and there is a lot of numbers like in example "1/5" etc. Or this numbers are just from 1 to 5? – Kamil Feb 12 '21 at 12:40
Just for whole numbers as per my DB Fiddle. You said you might have more than one `/`, so you need all the whole numbers from 1 to however many items might be stored separated by `/`s. So if your longest one is `123/456/789` you'd only need the numbers 1, 2 and 3. – Steve Lovell Feb 12 '21 at 12:44
It is used to help pick out the substring which corresponds to the number from that table. So when using the row with number `3`, for my last example, it would extract `789`. – Steve Lovell Feb 12 '21 at 12:45
OK, everythink is working fine :D, thank you very much, I check your answer as correct and usefull. Really really thanks for you – Kamil Feb 12 '21 at 12:49
Last one If I can :), If I want add this to new datatable then shoud I just type INSERT and this query put inside this? – Kamil Feb 12 '21 at 13:04
Apologies for my slow response. Yes, that's basically the way to do it. See this SO thread: https://stackoverflow.com/questions/6595252/mysql-creating-a-new-table-with-information-from-a-query#:~:text=You%20can%20do%20it%20like,table%20should%20be%20created%20from. – Steve Lovell Feb 12 '21 at 14:59

Creating new data table on existing one

2 Answers2