Transpose the column with removing the duplicate string

General

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 ??...........

Tags (1)
2 REPLIES
Senior Apprentice

Re: Transpose the column with removing the duplicate string

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

Re: Transpose the column with removing the duplicate string

Thanks Dieter