1 Reply Latest reply on Sep 9, 2021 3:03 PM by Nico Heinze

    INTRA-FIELD LIST UNPIVOT

    Alessandro Corti Seasoned Veteran

      Using PWC 10.4.0, is it possible to create a mapping where the source table is tt:

       

      SQL> Select a,b from tt 2

      /

      A           B

      ---------- ------------------------------

      1           John,Steve,Jim,Anne

      2           Karen,Betty,Rebecca,Tom,Henry

       

      And the mapping should write into the target the following records:

       

        A           B

      ---------- ----------

      1           John

      1           Steve

      1           Jim

      1           Anne

      2           Karen

      2           Betty

      2           Rebecca

      2           Tom

      2           Henry

        • 1. Re: INTRA-FIELD LIST UNPIVOT
          Nico Heinze Guru

          This and similar topics have been dealt with numerous times in the past. Basically PowerCenter doesn't do this out of the box, you have to implement your own solution.

           

          How this can be done depends on one central question: is the maximum number of repeated values restricted to some "reasonable" value, or may there be arbitrary numbers of single values in the source string?

           

          If, for example, your source strings will never contain more than 10 first names, then I would use an Expression transformation with a couple of variable ports.

          One set of variable ports contains the positions of the (in your case at most 9) delimiter characters in the source string, determined using invocations of the INSTR() function.

          Then you need the exact number of input values (to make sure that you never deal with more partial strings than there actually are in the source string).

          The second set of variable ports will hold the part strings between these positions, extracted using the SUBSTR() function.

          Then forward all those variabke port values from the second set which are not empty and are not beyond the actual number of partial strings in your current input value.

           

          If there may be an arbitrary number of partial strings, then I suggest you use a Java transformation to split up the input string into part strings. For everyone who's ever written more than a "Hello World" program in Java this is a breeze to implement. And if you don't know someone like that, just ask again, we can provide you with the code for such a Java transformation.

           

          There are of course other ways as well, but in terms of performance and maintainability these are the two best approaches. In my opinion.

           

          Regards,

          Nico