All Forums General
ritesh466 2 posts Joined 11/14
17 Jul 2015
Transpose the column with removing the duplicate string

Hi Everyone, 
Need help to acheive this below requirement.
Input Data  Setup :

Store_line_no store_no str1

1                  10           APPLE|Apricot

2                  20           Banana|Mango

2                  30           Orange|Orange|Strawberry

3                  50           Grapes|Guava

3                  40           Apricot|Guava

 

Require Output Setup :

 

Store_line_no str1

1                    APPLE|Apricot

2                    Banana|Mango|Orange|Strawberry                 

3                    Grapes|Guava|Apricot

                

Could you guys have any input for this ??...........

dnoeth 4628 posts Joined 11/04
17 Jul 2015

Splitting and removing duplicates is easy with in TD14+:

SELECT DISTINCT Store_line_no, token
FROM TABLE (STRTOK_SPLIT_TO_TABLE(tab.Store_line_no,tab.str1, '|')
   RETURNS (Store_line_no INT
           ,tokennum INTEGER
           ,token VARCHAR(100) CHARACTER SET UNICODE)
    ) AS t

And this is how this information should be stored.
 Why do you need to create denormalized data again?
 
If TD's XML-services are available you can use XMLAGG to do a group concat:

SELECT Store_line_no, 
   RTRIM(XMLAGG(token || '|' ORDER BY token) (VARCHAR(10000)),'|')
FROM
 (
   previous query
 ) AS dt
GROUP BY 1

 

Dieter

ritesh466 2 posts Joined 11/14
20 Jul 2015

Thanks Dieter

You must sign in to leave a comment.