Need help to acheive this below requirement.
Input Data Setup :
Could you guys have any input for this ??...........
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
,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:
RTRIM(XMLAGG(token || '|' ORDER BY token) (VARCHAR(10000)),'|')
) AS dt
GROUP BY 1