All Forums Database
19 Nov 2014
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:
dnoeth 4628 posts Joined 11/04
19 Nov 2014

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.
 

Dieter

20 Nov 2014

Hi dieter

My Teradata release version is TD14

vikas.jain 4 posts Joined 05/15
28 Jun 2015

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

dnoeth 4628 posts Joined 11/04
28 Jun 2015

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

 

 

Dieter

You must sign in to leave a comment.