Kusto | KQL: Expand dynamic column to all combinations of two ( Couples | Tuples )

Solution 1:

Here's a not too elegant nor efficient way, that uses an inner self join to get all combinations per Key

datatable(Key:string, Value:string)
[
    '1', 'A', 
    '2', 'B',
    '2', 'C', 
    '3', 'A', 
    '3', 'B', 
    '3', 'C', 
    '3', 'C'
]
| distinct Key, Value
| as hint.materialized=true T1
| join kind=inner T1 on Key
| where Value != Value1
| project Key, Tuple = tostring(array_sort_asc(pack_array(Value, Value1)))
| distinct Key, Tuple
| as hint.materialized=true T2
| union (
    T1
    | where Key !in ((T2 | project Key)) | project Key, Tuple = tostring(pack_array(Value))
)
| order by Key asc, Tuple asc