All Forums Analytics
adin.causevic 1 post Joined 08/14
11 Jan 2016
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 :)

CarlosAL 512 posts Joined 04/08
15 Jan 2016

Hi.
I think you'd better try with STRTOK_SPLIT_TO_TABLE().
HTH.
Cheers.
Carlos.

yuvaevergreen 93 posts Joined 07/09
11 May 2016
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)

 

You must sign in to leave a comment.