CODEWITHSUNDEEP
sqlteradata
navku
navku

Reputation: 190

Column to row conversion in teradata

I have source table as below

 PROGRAM_ID  SOURCE_TABLE
  1            A,B
  2            C,D,E
  3            E
  4            X,Y,Z,H,B

and I want target in below format

 PROGRAM_ID  SOURCE_TABLE
   1               A
   1               B
   2               C
   2               D
   2               E
   3               E
   4               X
   4               Y
   4               Z
   4               H
    4               B

I am getting number of comma in source query with below query CHARACTER(SOURCE_TABLE) - character(oreplace(SOURCE_TABLE,',','')) but I am not able to fetch records after first , second comma and so on.

Upvotes: 0

Views: 152

Answers (1)

dnoeth
dnoeth

Reputation: 60462

In TD14+ there are two built-in table functions to split strings, STRTOK_SPLIT_TO_TABLE and REGEXP_SPLIT_TO_TABLE:

with cte as
 (
   select PROGRAM_ID, SOURCE_TABLE
   from tab
 )
SELECT *
FROM TABLE (STRTOK_SPLIT_TO_TABLE(cte.PROGRAM_ID, cte.SOURCE_TABLE, ',')
     RETURNS (PROGRAM_ID INTEGER,
              tokennum INTEGER,
              SOURCE_TABLE VARCHAR(128) CHARACTER SET UNICODE)
            ) AS d

You need to modify the resulting data types to match the input (length and character set)

Upvotes: 1

Related Questions