Ordered concatenation of rows

Processors, Python, Spark, R
1

Dear One Data experts,

I have the following challenge to solve: given a datatable with test cases and execution steps, concatenate the execution step descriptions per test case, obeying an explicit order given by a index field in the test steps.

Example:

test_case_key step_description step_index
test_1 “some step description” 0
test_1 “last step” 2
test_1 “intermediate step” 1

should result in

test_case_key concatenated_description
test_1 “some step description, intermediate step, last step”

bonus points for any solution that does not use python or R :wink:

2

Not 100%, but I guess there is a nicer function for sorting based on another column:

> SELECT test_case_key,
> concat_ws(',', sort_array(collect_set(CONCAT(step_index,step_description)))) as concatenated_description
> FROM inputTable
> group by test_case_key
2 Likes
3

Thanks Laura! Based on your proposal I was able to adapt the query to do what I need

SELECT  i.test_case_key, 
concat_ws(', ', transform(sort_array(collect_list((i.step_index ,step_description))), a -> a.step_json)) as steps_json
FROM inputTable i group by  i.test_case_key