Archives of the TeradataForum

Message Posted: Fri, 12 Jun 2009 @ 15:02:34 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

do it with recursion

a small example:

     -- only Preparations
     create multiset table MYSAMPLEDATA,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT

     ( key1 integer not null,
       text char(20) not null)
     unique primary index (key1)
     ;

     commit
     ;

     insert into
     MYSAMPLEDATA
     values
     (1,'xxx,yyy,zzz')
     ;

     insert into
     MYSAMPLEDATA
     values
     (2,'aaa,bbb')
     ;

     insert into
     MYSAMPLEDATA
     values
     (3,'yyy')
     ;

     insert into
     MYSAMPLEDATA
     values
     (4,'')
     ;

     insert into
     MYSAMPLEDATA
     values
     (5,'ddd,eee,ddd,')
     ;

     select * from MYSAMPLEDATA;

     -- end of Preparations

     -- now select your data

     -- no_of_element is interesting for you,
     -- if you want to, which of the single values was which element no in the  list


     WITH recursive RT(key1,resttext,no_of_element) as
      (select ROOT.Key1, root.text, 1
       from MYSAMPLEDATA ROOT
       UNION ALL
       select
        PARENT.key1,
        substr(PARENT.resttext,
               position(',' in PARENT.resttext)+1,
               characters(trim(PARENT.resttext))
               ) as resttext
        ,no_of_element+1
       from
         RT PARENT
       where position(',' in PARENT.resttext)<>0
     )
     select
      key1,
      no_of_element,
      case when position(',' in resttext)<>0
           then substr(resttext,
              1,
              position(',' in resttext)-1
              )
           else
              resttext
           end as parttext
     from RT
     order by key1,no_of_element
     ;

     -- cleanup

     drop table MYSAMPLEDATA;
     commit;

	<Prev	Next>		<<First	<Prev	Next>	Last>>