All Forums Database
CCSlice 36 posts Joined 03/14
10 Oct 2014
Separation of columns into rows

Hi,
I am trying to find an easy way to transform data in a field to rows.  
 
For example,
 
ID                               INST           PACKAGE                  DEMOG
***                             ****           ********                  *******

ACM00125425211        TICKET       ENTERTAINMENT       U25; SDISC; PSTNG; PLAT
ACM00020521645        PASS          SPORTS                     U45; STIX; PSTNG; BBL
ACM00021352586        PASS          SPORTS                     U35; SPAK; BSKB
 
TO YIELD,
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.

Raja_KT 1246 posts Joined 07/09
10 Oct 2014

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

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

CCSlice 36 posts Joined 03/14
10 Oct 2014

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?
 
Thanks
 

Raja_KT 1246 posts Joined 07/09
10 Oct 2014

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 ...
union all
select id,inst,package,substr(demog,5,5) from yourtable 
 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
11 Oct 2014

What's your Teradata release?
In TD14 there's a STRTOK_SPLIT_TO_TABLE which does exactly what you need:

SELECT *
FROM TABLE (STRTOK_SPLIT_TO_TABLE(your_table.id,your_table.demog,';')
            RETURNS (outkey VARCHAR(...) CHARACTER SET UNICODE, 
                     tokennum INTEGER, 
                     token VARCHAR(...) CHARACTER SET UNICODE)
           ) AS d 

You can nest this in a Derived Table and then join back to get the other columns.

Dieter

CCSlice 36 posts Joined 03/14
14 Oct 2014

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.
 
CCSlice

dnoeth 4628 posts Joined 11/04
14 Oct 2014

Before TD14 it's much more complicated, see a solution I posted at:
https://forums.teradata.com/forum/database/stored-procedures-need-help-with-massaging-data-within-an-input-parameter

Dieter

CCSlice 36 posts Joined 03/14
15 Oct 2014

Thanks Dieter.  I will use and learn about this solution.

You must sign in to leave a comment.