CONCATE UNIQUE VALUES BASED ON ID IN INFORMAITCA

SCENARO/input,

COL1   COL2
100    ABC
101    PQR
100    ABC
100    OPQ
101    HDR
101    PQR

Expected OUTPUT:

COL1   COL2
100    ABC,OPQ
101    PQR,HDR

1 answer

  • answered 2022-04-30 05:30 Koushik Roy

    This is one of classic string aggregation issue. Pls follow below steps.

    1. using SRT order the data and get distinct. So, click enable distinct and then make sure you are ordering col1 first.
    2. using expression transformation concat the COL2. Create 5 ports - in_out means input+output, in_ means input only, v_ means variable port and so on.
    in_out_col1
    in_col2
    v_col2 = iif( in_out_col1=v_prev_col1, v_col2||',' ||in_col2,in_col2)
    v_prev_col1 = in_out_col1
    out_col2=v_col2
    
    1. Create an AGG, group by col1. Create a new column max_col2 and assign the max value to it.
    in_out_col1
    in_col2
    out_max_col2= MAX(in_col2)
    
    1. Connect in_out_col1 to col1 and out_max_col2 to col2 for your desired data.

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum