strtok - How to group substrings in Teradata 14? -

i have following table in teradata 14 , not allowed write procedures , functions myself, can use strtok, strtok_split_to_table etc

id  property 1   1234x (yel), 2225y (red), 1234x (gre), 2 3   1222y (pin),  4   1134e (yel), 4565y (whi), 1134e (red), 2222y (red),  

how can group above table each object have attributes listed in 1 brackets

id  property 1   1234x (yel gre), 2225y (red),  2    3   1222y (pin ), 4   1134e (yel red), 4565y (whi), 2222y (red),  

the property code 5 character string, e.g. 1222y . color code 3 character , e.g. pin

---

i tried using this solution got error a column or character expression larger max size

in addition tried strtok_split_to_table , able create modified table, not how proceed that

why store denormalized data in rdbms , process create worse denormalized output?

modifying solution link posted utilize strtok_split_to_table instead of recursion:

select    id,    max(case when newpos = 1 , newgrp <> '(),' newgrp else '' end) ||    max(case when newpos = 2 newgrp else '' end) ||    max(case when newpos = 3 newgrp else '' end) ||    max(case when newpos = 4 newgrp else '' end) ||    max(case when newpos = 5 newgrp else '' end) ||    max(case when newpos = 6 newgrp else '' end)    -- add many cases needed  (     select       id,       row_number()       on (partition id            order newgrp) newpos,      trim(a || ' (' ||      max(case when tokennum = 1 b || ' ' else '' end) ||      max(case when tokennum = 2 b || ' ' else '' end) ||      max(case when tokennum = 3 b || ' ' else '' end) ||      max(case when tokennum = 4 b || ' ' else '' end) ||      max(case when tokennum = 5 b || ' ' else '' end) ||      max(case when tokennum = 6 b || ' ' else '' end)      -- add many cases needd      ) || '), ' newgrp         (       select         id, tokennum,         trim(substring(token 1 position('(' in trim(token)||'(') - 1)) a,         trim(trailing ')' substring(token position('(' in token) + 1)) b               table( strtok_split_to_table(vt.id, vt.property, ',')         returns (id int,                  tokennum int,                   token varchar(30) character set unicode                 )              ) dt     ) dt    group id,  ) dt group id; 

if got access tdstats.udfconcat function can further simplified (but there's way control order of properties:

select id,     case      when trim(tdstats.udfconcat(' ' || || ' ' || b)) || ',' <> '(),'      trim(tdstats.udfconcat(' ' || || ' ' || b)) || ','      else ''    end   (    select      id,      trim(substring(token 1 position('(' in trim(token)||'(') - 1)) a,      '('|| otranslate(tdstats.udfconcat(trim(trailing ')' substring(token position('(' in token) + 1))), ',', ' ') || ')'as b         table( strtok_split_to_table(vt.id, vt.property, ',')      returns (id int,               tokennum int,                token varchar(30) character set unicode              )           ) dt    group id,  ) dt group id; 

most of work fiddling spaces , commas in right place requested output.

still never store data such in rdbms.