Using Variables in BODS

[Former Member]

Hi Experts,

i have a senario lets consider sample source table

pro_id     item_number     store_name

101          10                    store1

102          10                    store1

101          10                    store2

101          10                    store3

102          10                    store2

i need ouput as

pro_id     item_number     store_name

101          10                         store1, store2, store3

102          10                         store1, store2

plz help regarding this ....

Thanks & Regards,

Mahe

[mohan_salla]

Use Pivot transform to resolve this..

[titto_antony]

Hi Mahe,

If the pro_id is constant you can create a custum function to concatinate the storename based on the Pro_id Filed. as given below. Put a row_num to each row in the flow and take out the column with max row_num based on grp pro_id  to filter the record with all the store name after the custom function.

if  ($Id=101)
$LocalStoreName =$Input_SotreName||','||$LocalStoreName;
if  ($Id=102)
$LocalStoreName2 =$Input_SotreName||','||$LocalStoreName2;

$Output_StoreName=$LocalStoreName;
$Output_StoreName2=$LocalStoreName2;
Return $LocalStoreName;