Single row to multiple row

Analytics

Single row to multiple row

Hello everyone

I would like some help to convert a single row into multiple rows

I have something like this

store | item | quantity

001 | A | 4

001,002 | B | 5

004,006,003 | C | 12

And i would like to do something like this

001 | A | 4

001 | B | 5

002 | B | 5

004 | C | 12

006 | C | 12

003 | C | 12

I tried with regexp_split_to_table function but didn't manage to do what i need

Thx for your help :)

2 REPLIES
Junior Supporter

Re: Single row to multiple row

Hi.

I think you'd better try with STRTOK_SPLIT_TO_TABLE().

HTH.

Cheers.

Carlos.

Enthusiast

Re: Single row to multiple row

SEL A.TOKEN1 AS STORE,B.ITEM,B.QUANTITY FROM 
(sel D.OUTKEY,D.TOKEN1 FROM TABLE (STRTOK_SPLIT_TO_TABLE(T2.ITEM,T2.STORE,',')
RETURNS (OUTKEY VARCHAR(10),TOKENNUM INTEGER,TOKEN1 VARCHAR(20) CHARACTER SET UNICODE)) AS D
GROUP BY 1,2
) A,T2 B
WHERE TRIM(B.ITEM)=TRIM(A.OUTKEY)