CODEWITHSUNDEEP
sqlanalyticsazure-data-explorer
Anusha
Anusha

Reputation: 23

Comma separated values to individual columns in Kusto

I have two columns in kusto table, The second column has comma separated values, and I need the values to be projected as individual columns. The comma sepearted values in second column, changes for each environment, and it cannot be hardcoded.

Input:

key    val
key1   val1,val2,val3,val4
key2   val8,val2,val9,val4
key3   val8,val1,val9,val5

output:

keyhdr valhdr1 valhdr2 valhdr3 valhdr4    
key1   val1    val2    val3    val4
key2   val8    val2    val9    val4
key3   val8    val1    val9    val5

Upvotes: 1

Views: 4930

Answers (1)

Ohad Bitton
Ohad Bitton

Reputation: 515

You can first use the split function to create an array from the tabular expression, then you can use array indexing to extend it into columns. Note that if no value is found in the index it will be filled with an empty string. This will give you the output you wanted :

datatable(key:string, val:string)
[
   "key1","val1,val2,val3,val4",
   "key2", "val8,val2,val9,val4",
   "key3",  "val8,val1,val9,val5"
]
| extend all=split(val,',')
| extend valhdr1  = all[0]
| extend valhdr2  = all[1]
| extend valhdr3  = all[2]
| extend valhdr4  = all[3]
| project key,valhdr1,valhdr2,valhdr3,valhdr4

Upvotes: 1

Related Questions