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

user_17322246 0
Viewed 299

I have a scenario where I am trying to create a view that shows me all the unique couples of values per key. For example:

datatable(Key:string, Value:string)[
'1', 'A', 
'2', 'B',
'2', 'C', 
'3', 'A', 
'3', 'B', 
'3', 'C', 
'3', 'C']
| sort by Key, Value asc
| summarize Tuples=make_set(Value) by Key

Result:

Key Tuples
1   ["A"]
2   ["B","C"]
3   ["A","B","C"]

Desired Result:

Key Tuples
1   ["A"]
2   ["B","C"]
3   ["A","B"]
3   ["A","C"]
3   ["B","C"]

How can I achieve this in KQL?

kusto-explorer kql azure-data-explorer
1 Answers

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
Key Tuple
1 ["A"]
2 ["B","C"]
3 ["A","B"]
3 ["A","C"]
3 ["B","C"]
user_78618070
Related