Separation of columns into rows

Database

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.

7 REPLIES

Re: Separation of columns into rows

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

Re: Separation of columns into rows

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

Re: Separation of columns into rows

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 

N/A

Re: Separation of columns into rows

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.

Re: Separation of columns into rows

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

N/A

Re: Separation of columns into rows

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

Re: Separation of columns into rows

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