how to split the data in snowflake and put them in respective columns

Question

EXAMPLE:

id  core           primary         secondary
101 4355|6755     4355|7866 
102 8566|6755      8566              8566
```````````````````````````````````````````````````````````````````````````````

I need to split the data into another table. One with only codes and indicators. All the codes should be in one column under codes and their respective codes as an indicator in other columns as below:
``````````````````````````````````````````````````````````````````````````

id      codes       core_ind        primary_ind secondary_ind
101     4355        Y                   Y   
101     6755        Y           
101     7866                            Y   
102     8566        Y                   Y           Y
102     6755        Y

I am able to split the columns using lateral but not sure how to put the indicators the their respective columns. Can you suggest anything? Below is the code I used for splitting. Using this code I am able to put all the codes and id's under their respective columns. Now, I need to put the indicators I mentioned above

SELECT id, c.value::varchar AS codes 
FROM table
   ,lateral flatten (input => split(core, '|')) c
UNION ALL 
SELECT id, d.value::varchar AS codes 
FROM  table 
   ,lateral flatten (input => split(primary, '|')) d
UNION ALL 
SELECT id, e.value::varchar AS codes 
FROM table
    ,lateral flatten (input => split(secondary, '|')) e

Thanks in Advance!!

Simeon Pilgrim · Answer 1 · 2020-05-06T20:24:27.353

not that I am a super fan of the union's for now leaving those alone it can be made to work like this:

with data as (
    select * from values (101, '4355|6755', '4355|7866', NULL),
                         (102, '8566|6755', '8566'     , 8566)
         v(id, core, primary, secondary)
), exploded as (      
    SELECT id, 'c' as type, c.value::varchar AS codes 
    FROM data
       ,lateral flatten (input => split(core, '|')) c
    UNION ALL 
    SELECT id, 'p' as type, d.value::varchar AS codes 
    FROM  data 
       ,lateral flatten (input => split(primary, '|')) d
    UNION ALL 
    SELECT id, 's' as type, e.value::varchar AS codes 
    FROM data
        ,lateral flatten (input => split(secondary, '|')) e
)
select id
    ,codes
    ,iff(sum(iff(type='c',1,0))>0,'Y','') as core_ind
    ,iff(sum(iff(type='p',1,0))>0,'Y','') as primary_ind
    ,iff(sum(iff(type='s',1,0))>0,'Y','') as secondary_ind
from exploded
group by 1,2
order by 1,2;

this gives:

ID  CODES   CORE_IND   PRIMARY_IND   SECONDARY_IND
101 4355    Y          Y    
101 6755    Y       
101 7866               Y    
102 6755    Y       
102 8566    Y          Y            Y

so this can also be done with UNPIVOT then FLATTEN, does not feel much better:

with data as (
   select * from values (101, '4355|6755', '4355|7866', NULL),
                         (102, '8566|6755', '8566'     , '8566')
         v(id, data_a, data_b, data_c)
), unpivoted as (
    select * 
    from data unpivot(code for type in (data_a, data_b, data_c))
), flattened as (
    select id, type, f.value as codes
    from unpivoted
        ,lateral flatten (input => split(code, '|')) f
)
select id
    ,codes
    ,iff(sum(iff(type='DATA_A',1,0))>0,'Y','') as core_ind
    ,iff(sum(iff(type='DATA_B',1,0))>0,'Y','') as primary_ind
    ,iff(sum(iff(type='DATA_C',1,0))>0,'Y','') as secondary_ind
from flattened
group by 1,2
order by 1,2;

after I thought about it more, the unions is about the only way I can think of breaking them up, to allow the re-grouping on the ID. — Simeon Pilgrim, May 06 '20 at 05:59
Yes. I tried other ways but it didn't work. Let me know if their are any better ways of doing it as well. I appreciate your help. — sony, May 06 '20 at 20:10
just add another way to do it via UNPIVOT and then a single FLATTEN, I suspect over a larger dataset your way might perform better, and the unions can happen in parallel — Simeon Pilgrim, May 06 '20 at 20:25

how to split the data in snowflake and put them in respective columns

1 Answers1