Split The Column which is delimited into separate Rows

Database
Enthusiast

Split The Column which is delimited into separate Rows

Hi

Can any one help me in solving this. In a table for the columns Division and Node , I have the data like this right now

Division                                 Node

West                     RMACA-RMACB-RMACC-RMACD

Central                  PO63A-PO63B-PO63C-PO63D

But i need like this

Division                        Node

West                          RMACA

West                          RMACB

West                          RMACC

West                          RMACD

Central                       PO63A

Central                       PO63B

Central                       PO63C

Central                       PO63D

Thanks in advance

Naveen


Tags (1)
4 REPLIES
Senior Apprentice

Re: Split The Column which is delimited into separate Rows

Hi Naveen,

this question has been asked numerous times :-)

What's your Teradata release?

Since TD14 there's STRTOK_SPLIT_TO_TABLE:

SELECT * 
FROM TABLE (STRTOK_SPLIT_TO_TABLE(your_table.Division, your_table.Node, '-')
RETURNS (outkey VARCHAR(10) CHARACTER SET UNICODE
,tokennum INTEGER
,token VARCHAR(30) CHARACTER SET UNICODE)
) AS dt

The size of both outkey and token should be changed to match the actual length.

Enthusiast

Re: Split The Column which is delimited into separate Rows

Hi dieter

My Teradata release version is TD14

Re: Split The Column which is delimited into separate Rows

Hi dieter,

How to do the exact reverse of this? As in to to get key and concat rows into single columns.

Thanks

Senior Apprentice

Re: Split The Column which is delimited into separate Rows


What's your Teradata release? Are XML services available?

SELECT * FROM dbc.FunctionsV
WHERE FunctionName = 'XMLAGG';

SELECT Column_A,  
TRIM(TRAILING ',' FROM (XMLAGG(Column_B || ','
ORDER BY ColumnB
) (VARCHAR(10000))))
FROM tab
GROUP BY 1