0

I need one help regarding column generator based on value from other column. I have one table as below.

CREATE TABLE test1
(
    `C1`              INT,
    `C2`             VARCHAR(120),
    `C3`                 VARCHAR(50),
    `C4`                VARCHAR(20),
    `C5`                 VARCHAR(50)
);

Insert into test1 (C1,C2,C3,C4,C5) values (1071,'TEST01','51','464',null);
Insert into test1 (C1,C2,C3,C4,C5) values (1071,'TEST02','9','109',null);
Insert into test1 (C1,C2,C3,C4,C5) values (970,'TEST03','1,2','454,41',null);
Insert into test1 (C1,C2,C3,C4,C5) values (950,'TEST04','1,2','253,255,254',null);
Insert into test1 (C1,C2,C3,C4,C5) values (792,'TEST05','1,2','536','57,65,66');

The output is below:

C1      C2      C3      C4              C5
1071    TEST01  51      464 
1071    TEST02  9       109 
970     TEST03  1,2     454,41  
950     TEST04  1,2     253,255,254 
792     TEST05  1,2     536             57,65,66

Now the select query should display all records + additional columns like C3_NEW, C4_NEW and C5_NEW. These new columns are based on value of C3,C4 and C5.

Example: If C3 has two value i.e. 1,2 and C4 has 454,41 Then C3_NEW should have created based on 1 and then 2, and C4_NEW should be created based on 454 and 41. So additional rows based on values.

Below is the desired output, I am looking for.

C1      C2      C3      C4          C5          C3_NEW  C4_NEW  C5_NEW
1071    TEST02  9       109                     9       109 
1071    TEST01  51      464                     51      464 
970     TEST03  1,2     454,41                  1       41  
970     TEST03  1,2     454,41                  1       454 
970     TEST03  1,2     454,41                  2       41  
970     TEST03  1,2     454,41                  2       454 
950     TEST04  1,2     253,255,254             1       253 
950     TEST04  1,2     253,255,254             1       255 
950     TEST04  1,2     253,255,254             1       254 
950     TEST04  1,2     253,255,254             2       253 
950     TEST04  1,2     253,255,254             2       255 
950     TEST04  1,2     253,255,254             2       254 
792     TEST05  1,2     536         57,65,66    1       536     57
792     TEST05  1,2     536         57,65,66    1       536     65
792     TEST05  1,2     536         57,65,66    1       536     66
792     TEST05  1,2     536         57,65,66    2       536     57
792     TEST05  1,2     536         57,65,66    2       536     65
792     TEST05  1,2     536         57,65,66    2       536     66

In Oracle, below query, gives desired final output:

Select
    C1, C2, C3, C4, C5,
    decode(C3_NEW, 'NONE', NULL, C3_NEW)  C3_NEW,
    C4_NEW,
    regexp_substr(sp_pub_sub.C5,'[^,]+',1,column_value) C5_NEW
from
    (Select C1, C2, C3, C4, C5, C3_NEW,regexp_substr(sp_C3.C4,'[^,]+',1,column_value) C4_NEW
     from
     (select  C1, C2, C3, C4, C5, regexp_substr(C3,'[^,]+',1,column_value) C3_NEW 
     from  (select C1, C2, replace(C5, ' or ',',') as C5, replace(C3, ' or ',',') as C3, replace(C4, ' or ',',') as C4 from test1) t
            ,table(cast(multiset(select level from dual connect by level <= 1+length(C3) - length(replace(C3,',')) ) as sys.OdciNumberList)) ) sp_C3
            ,table(cast(multiset(select level from dual connect by level <= 1+length(sp_C3.C4) - length(replace(sp_C3.C4,',')) ) as sys.OdciNumberList)) ) sp_pub_sub
            ,table(cast(multiset(select level from dual connect by level <= 1+length(sp_pub_sub.C5) - length(replace(sp_pub_sub.C5,',')) ) as sys.OdciNumberList))
 order by 1 desc,2,3,4,5,6;

Now, mysql does not have multiset or level, so how do I get the same output in mysql. Please go easy on me, as I am not a developer :-)

Thanks in advance

VikiT
  • 78
  • 7
  • Do you have a PRIMARY KEY? – Strawberry Aug 27 '18 at 09:37
  • and see https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – Strawberry Aug 27 '18 at 09:38
  • Thanks @Strawberry, No, it does not have a primary key. I saw the link, but not sure what to take from it. – VikiT Aug 27 '18 at 09:45
  • If it doesn't have a PRIMARY KEY, then it's not really a table, and therefore arguably beyond the scope of this tag. If you're not sure what to take from the suggested link, then I'm afraid you've exhausted my knowledge on the matter - but perhaps others can advise further. – Strawberry Aug 27 '18 at 09:58
  • Hi Starberry, my apologies, it does have primary key and foreign key constraints, but in the original table and not in the sample that I provided, that why I said it doesn't have primary key. I didn't put in the above query as the primary key column was not called by query. e.g In above above, there would be a new column called ID with primary key. – VikiT Aug 27 '18 at 10:25

0 Answers0