I am trying to find an easy way to transform data in a field to rows.
ID INST PACKAGE DEMOG
*** **** ******** *******
ACM00125425211 TICKET ENTERTAINMENT U25; SDISC; PSTNG; PLAT
ACM00020521645 PASS SPORTS U45; STIX; PSTNG; BBL
ACM00021352586 PASS SPORTS U35; SPAK; BSKB
ACM00125425211 TICKET ENTERTAINMENT U25
ACM00125425211 TICKET ENTERTAINMENT SIDSC
ACM00125425211 TICKET ENTERTATIMENT PSTNG
ACM00125425211 TICKET ENTERTAINMENT PLAT
ACM00020521645 PASS SPORTS U45
ACM00020521645 PASS SPORTS STIX
ACM00020521645 PASS SPORTS PSTNG
ACM00020521645 PASS SPORTS BBL
ACM00021352586 PASS SPORTS U35
ACM00021352586 PASS SPORTS SPAK
ACN00021352586 PASS SPORTS BSKB
Thanks in advance for your help.
select id,inst,package,(character_length(demog) -character_length(regexp_replace(demog,'[^A-Z 0-9]+','',1,0,'i))+1) m,regexp_substr(demog,'[^;]+',1,day_of_calendar) from
yourtable,sys_calendar.calendar where day_of_calendar between 1 and m
Thanks Raja for this. I do not understand how the sys.calendar figures into this solution. Also the portion of the code where you have:
-character_length(regexp_replace(demog,'[^A-Z 0-9]+','',1,0,'i))+1) m
The "m" will be the number of days in the month. Is this a factor to be multiplied? Or is it an error?
m is not no of days in month. ok then try to work out with simple substr like
select id,inst,package,substr(demog,1,3) from yourtable ...
select id,inst,package,substr(demog,5,5) from yourtable
What's your Teradata release?
In TD14 there's a STRTOK_SPLIT_TO_TABLE which does exactly what you need:
FROM TABLE (STRTOK_SPLIT_TO_TABLE(your_table.id,your_table.demog,';')
RETURNS (outkey VARCHAR(...) CHARACTER SET UNICODE,
token VARCHAR(...) CHARACTER SET UNICODE)
) AS d
You can nest this in a Derived Table and then join back to get the other columns.
Hi Dieter... The version of Teradata I have is 13.10 with a SQL Assistant of 14.01. I don't have permissions to create tables so if I need a table, it would have to done as a CTE.. (with...).
Dieter is another method that I can use? In some cases I may have up to 35 values in the Demog field that would need to seperated.
Before TD14 it's much more complicated, see a solution I posted at: