All Forums Database
Raja4798 2 posts Joined 03/09
26 Feb 2014
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.

dnoeth 4628 posts Joined 11/04
26 Feb 2014

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

 

Dieter

Raja_KT 1246 posts Joined 07/09
27 Feb 2014

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,

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Raja4798 2 posts Joined 03/09
27 Feb 2014

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.

taruntrehan 43 posts Joined 10/12
28 Feb 2014

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.

Regards,
Tarun Trehan
http://allzhere.in

You must sign in to leave a comment.