Transpose Columns with Multivalues to Rows - Need Help

Analytics
Highlighted
Tourist

Transpose Columns with Multivalues to Rows - Need Help

Hi All,

 

I have a below table and have to convert columns to row. Please guide"

 

ID                                    SPLIT_ID

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

45678A                            '','A','B','C','D'

 

I want the table to look like 

 

ID                                      SPLIT_ID

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

45678A                             45678

45678A                              45678A

45678A                              45678B

45678A                               45678C

45678A                                45678D

 

The number of split id might differ.It can be sometimes 'B','C'  or "A','B','D','E'. Please suggest

2 REPLIES
Teradata Employee

Re: Transpose Columns with Multivalues to Rows - Need Help

Use STROK_SPLIT_TO_TABLE function to convert columns to rows in a subquery

 

45678A    1    ''

45678A    2    'A'

45678A    3    'B'

45678A    4    'C'

45678A    5    'D'

 

Then use those rows to format your desired result rows in the outer query.

Teradata Employee

Re: Transpose Columns with Multivalues to Rows - Need Help

Given this was 3 weeks ago, you may have already found an answer, but here is one anyway.  And this does use the

strtok_split_to_table function, but I included all the syntax.  First I created a table and loaded 3 examples.

create table rs150000.foo

 (

ID varchar(6),

SPLIT_ID varchar(25)

)

primary index(ID)

;

insert into rs150000.foo values('45678A', ''',''A'',''B'',''C'',''D''');

insert into rs150000.foo values('56789B', ''',''B'',''C''');

insert into rs150000.foo values('67890A', ''',''A'',''B'',''D'',''E''');

So select * from rs150000.foo looks like this:

ID SPLIT_ID

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

45678A ','A','B','C','D'

67890A ','A','B','D','E'

56789B ','B','C'

The syntax of the function seems to be looking for a numeric id, so I modified the 1st table, casting the 1st field as an integer after triming the alpha at the end:

create table rs150000.foo_mod

as

(select cast(substring(ID from 1 for 5) as int) as ID

           ,SPLIT_ID

      from rs150000.foo)

with data;

You can get your final result by just running a single step, but I'm going to show you an intermediate step so you'll better understand what's going on.  The following query comes close, but doesn't have the formatting you need.  The results are below the query.

SELECT d.outkey

         ,d.tokennum

         ,d.token

FROM TABLE

         (strtok_split_to_table(foo_mod.id

                                             ,cast(foo_mod.split_id as varchar(32000))

                                             ,cast(',' as varchar(64)))

RETURNS (outkey integer

                   ,tokennum integer

                  ,token varchar(6)character set unicode)

) as d;

outkey tokennum token

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

67890  1               '

45678  1               '

56789  1               '

67890  2              'A'

45678  2              'A'

56789  2              'B'

67890  3              'B'

45678  3              'B'

56789  3              'C'

67890  4              'D'

45678  4              'C'

67890  5              'E'

45678  5              'D'

So you wrap the prior query in a derived table, and concatenate your outkey and token while applying a couple of trim functions.  The query is followed by the results.

select outkey as ID

           ,rtrim((cast(outkey as varchar(5))||ltrim(token,'''')),'''') as SPLIT_ID

 from (SELECT d.outkey

 ,d.tokennum

,d.token

FROM TABLE

(strtok_split_to_table(foo_mod.id

                                    ,cast(foo_mod.split_id as varchar(32000))

                                    ,cast(',' as varchar(64)))

RETURNS (outkey integer

                  ,tokennum integer

                  ,token varchar(6)character set unicode)

) as d

) as a;

ID         SPLIT_ID

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

67890   67890

45678   45678

56789   56789

67890   67890A

45678   45678A

56789   56789B

67890   67890B

45678   45678B

56789   56789C

67890   67890D

45678   45678C

67890   67890E

45678   45678D

And just FYI, this use case is a lot simpler if the data is in an Aster database.