split and duplicate specific columns into rows

Analytics
Enthusiast

split and duplicate specific columns into rows

Hi,

 

I have tables with rows in the following format

IDTypeColorPrice
1ShirtBlack|White10
2PantsGreen|Blue15
3SocksWhite

5

 

Is there a way to split the columns and duplicate to rows where the color value have "|" in it to the follwing format?

 

IDTypeColorPrice
1ShirtBlack10
1ShirtWhite10
2PantsGreen15
2PantsBlue15
3SocksWhite5

 

Thanks!


Accepted Solutions
Highlighted
Senior Apprentice

Re: split and duplicate specific columns into rows

As @Waldar said no, you must code those three items. They're part of the syntax for the STRTOK_SPLIT_TO_TABLE function.

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
1 ACCEPTED SOLUTION
10 REPLIES
Senior Apprentice

Re: split and duplicate specific columns into rows

Hi,

You can use the STRTOK_SPLIT_TO_TABLE function.

Try the following:

CREATE SET VOLATILE TABLE vt1
(colID	INTEGER
,colTYPE VARCHAR(10)
,colColor VARCHAR(30)
,colPrice INTEGER)
PRIMARY INDEX(colid)
ON COMMIT PRESERVE ROWS;

INSERT INTO vt1 VALUES(1,'Shirt','Black|White',10);
INSERT INTO vt1 VALUES(2,'Pants','Green|Blue',15);
INSERT INTO vt1 VALUES(3,'Socks','White',5);

SELECT dt2.*,vt1.colprice
FROM (SELECT dt1.*
      FROM TABLE(STRTOK_SPLIT_TO_TABLE(vt1.colid,vt1.colcolor,'|')
      RETURNS (colid INTEGER,tokennum INTEGER,colcolor VARCHAR(30)) )AS dt1
     ) AS dt2
INNER JOIN vt1	
  ON dt2.colid = vt1.colid
ORDER BY 1,2;

HTH

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: split and duplicate specific columns into rows

Thanks Dave!

 

Is there a way to do this without defining the structure of the table? In case I need to use it on a large table or in different queries with different tables?

Senior Apprentice

Re: split and duplicate specific columns into rows

Hi,

 

Just so I know we're talking about the same thing...

 

In my answer, the "CREATE TABLE..." and INSERT statements were only there to set up test data.

 

The SELECT statement that I provided was to allow the splitting and then join back to the source table so that you could display the 'price' column, which was part of your requirement.

 

I don't think there're many ways (if any) that you can reduce the SELECT statement.

 

HTH

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: split and duplicate specific columns into rows

Hi,

 

I meant the 

RETURNS (colid INTEGER,tokennum INTEGER,colcolor VARCHAR(30))

is there a way to split the rows without defining this? 

 

Thanks,

Yonatan

Teradata Employee

Re: split and duplicate specific columns into rows

I don't think so, but this part is always three returning columns.

Highlighted
Senior Apprentice

Re: split and duplicate specific columns into rows

As @Waldar said no, you must code those three items. They're part of the syntax for the STRTOK_SPLIT_TO_TABLE function.

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: split and duplicate specific columns into rows

Thanks a lot, I will use it :)

Enthusiast

Re: split and duplicate specific columns into rows

I've tried to use the function inside my query and i get error 6881:STRTOK_SPLIT_TO_TABLE withou further explanation.

This is my query: 

 

SELECT nv.*
FROM TABLE (STRTOK_SPLIT_TO_TABLE(NV_FILTERED.item_id,NV_FILTERED.aspct,'|')RETURNS (ITEM_ID DECIMAL(18,0), tokennum INTEGER,aspct  VARCHAR(256)))AS nv
ORDER BY 1

 NV_FILTERED table is in format of

ITEM_IDaspct
332302219163Network
132699935007Network
123468318377Network|Model Number
292803744762Color|Storage Capacity|Model Number
153246638331Network|Color
323531323773Brand
113317395839Network
283236815474Network
264015092335Color
372488430905Network

 

What can be the issue?

Senior Apprentice

Re: split and duplicate specific columns into rows

Hi,

 

Either the function (or the documentation) are being very unhelpful. The problem is in the data types being used, although according to my reading of the documentation they should be acceptable.

 

The following returns an answer set on both 16.0 and 16.20:

SELECT nv.*
FROM TABLE (STRTOK_SPLIT_TO_TABLE(CAST(NV_FILTERED.item_id AS VARCHAR(19)),NV_FILTERED.aspct,'|')
      RETURNS (ITEM_ID VARCHAR(19), tokennum INTEGER,aspct  VARCHAR(256)))AS nv
ORDER BY 1

The function appears to not like the DECIMAL(18,0) column, although the documentation says the 'inkey' should be NUMERIC or VARCHAR. The function also doesn't like BIGINT.

 

I would suggest that you raise an incident on TaYS (TD support) with two points:

1) the error message from the function does not help in problem diagnosis.

2) the function doesn't appear to accept BIGINT or DECIMAL(18,x) data type columns

 

HTH

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com