0

I have data like below

Id | Data                                                                  |Parent Id
----------------------------------------------------------------------------------
1  | IceCream # Chocolate # SoftDrink              |0
2  | Amul,Havemore#Cadbary,Nestle#Pepsi     |1
3  | Party#Wedding                                                |0

I want to split this data in below format where row 2 is dependent on row 1. I have added ParentId which is use to find dependency.

IceCream  | Amul      | Party
IceCream  | Havemore  | Party
IceCream  | Amul      | Wedding
IceCream  | Havemore  | Wedding
Chocolate | Cadbery   | Party
Chocolate | Nestle    | Party
Chocolate | Cadbery   | Wedding
Chocolate | Nestle    | Wedding
SoftDrink | Pepsi     | Party
SoftDrink | Pepsi     | Wedding

I have used unnest(string_to_array) to split string but unable to traverse through loop to make this combination.

3
  • Can you post some details of the logic used to obtain that result? Commented Oct 28, 2019 at 12:27
  • 1
    How do you know which row needs which delimiter to split the values? And where does this "flag" come from that "is known to you" Commented Oct 28, 2019 at 13:00
  • I believe you need a larger number of rows to explain your problem. + important: Are there ANY other columns in this table? Please be aware that in SQL tables are "un-ordered sets" so you should not assume that "row 2" will be row encountered after "row 1"; they might be process in a completely different order. Commented Oct 30, 2019 at 3:53

1 Answer 1

1

The is a very "unstable",like sitting on a knife edge and could easily fall apart. It depends on assigning values for each delimited value and then joining on those values. Maybe those flags that are known to you (but unfortunately not us) can stabilize it. But it does match your indicated expectations. It uses the function regexp_split_to_table rather than unnest to split the delimiters.

with base (num, list) as 
   ( values (1,'IceCream#Chocolate#SoftDrink')
          , (2,'Amul,Havemore#Cadbary,Nestle#Pepsi')
          , (3,'Party#Wedding')
   )  
   , product as
     (select p, row_number(*) over() pn
        from ( 
              select regexp_split_to_table(list,'#') p
                from base
               where num=1
             ) x
     )
   , maker as
     (select  regexp_split_to_table(m, ',') m, row_number(*) over() mn
        from (
              select regexp_split_to_table(list,'#') m
                from base
               where num=2
             ) y
     )
   , event as
     ( select regexp_split_to_table(regexp_split_to_table(list,'#'), ',') e
         from base
        where num=3
     ) 
select p as product
     , m as maker
     , e as event
  from (product join maker on pn = mn) cross join event e
  order by pn, e, m;

Hope it helps.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.