Change One Row Data To Multiple Rows.

Database

Change One Row Data To Multiple Rows.

Hello Gurus,

I have a requirement that I need to change one row to multiple rows by using delimit ##

 

Current Data in Table:

 

 NAME                                               COLUMN1

Chandu      200##ABC_100##102340##XYZ_4506##754##89122##244

 

Below is the required Output:

 

 NAME            COLUMN1

Chandu              200

Chandu          ABC_100

Chandu          102340

Chandu         XYZ_4506

Chandu             754

Chandu          89122

Chandu            244

 

Please Help.

Thanks in advance.

4 REPLIES
Senior Apprentice

Re: Change One Row Data To Multiple Rows.

What's your TD release?

In TD14 there's a STRTOK_SPLIT_TO_TABLE function:

SELECT * 
FROM
TABLE (STRTOK_SPLIT_TO_TABLE(myTable.name,myTable.col1,'#')
RETURNS (outkey VARCHAR(20) CHARACTER SET UNICODE ,
tokennum INTEGER,
token VARCHAR(30) CHARACTER SET UNICODE)) AS dt

I never succeeded to use non-Unicode data for the 2nd parameter, so if your col1 is LATIN you must translate it:

SELECT * 
FROM
TABLE (STRTOK_SPLIT_TO_TABLE(myTable.name,TRANSLATE(myTable.col1 USING latin_to_unicode),'#')
RETURNS (outkey VARCHAR(20) CHARACTER SET UNICODE ,
tokennum INTEGER,
token VARCHAR(30) CHARACTER SET UNICODE)) AS dt
Enthusiast

Re: Change One Row Data To Multiple Rows.

In the older version, you can try  use union all of select statements by  use of substring delimited by ## for the second field- column1. Get the name and first substring of 200 and then union all with the second substring..... (The first field remains the same). 

Cheers,

Re: Change One Row Data To Multiple Rows.

Hey Thank u sooo much Dnoeth.. But my TD version is 13.10.0.04. So could you please help me resolve this in this version. In this version there is no STROKE_SPLIT_TO_TABLE function.

Enthusiast

Re: Change One Row Data To Multiple Rows.

You can try to iterate through rows from first table and split the rows by ## and insert them in a separate table. I did this in Sybase. The ready made functions should be doing something on similar lines. 

However, on another note, In case you are retrieving results in some java/phython layer; you can split them there as you will have lots of utlity functions available to accomplish this smoothly.