I have a table , here is what the table looks like:

let raw = datatable(id:string, Time:string, p90:long, p95:long, p99:long)
[
    "A", "2025-01-06", 38, 124, 156,
    "B", "2025-01-06", 66, 120, 130,
    "A", "2025-01-13", 55, 99, 150, 
    "B", "2025-01-13", 77, 100, 130, 
];

I want to aggregate the data base on time and mere the line with ID_Percentile metrics, like I want the data to be below: Note, the id value could be dymanic, so it could be A,B,C in other time or, A,B,C,D etc.

Time        A_p90   A_p95   A_p99   B_p90   B_p95   B_p99
2025-01-06  38      124     156     66      120     130
2025-01-13  55      99      150     77      100     130

May I know how to write the kusto query? thank you.

I have a table , here is what the table looks like:

let raw = datatable(id:string, Time:string, p90:long, p95:long, p99:long)
[
    "A", "2025-01-06", 38, 124, 156,
    "B", "2025-01-06", 66, 120, 130,
    "A", "2025-01-13", 55, 99, 150, 
    "B", "2025-01-13", 77, 100, 130, 
];

I want to aggregate the data base on time and mere the line with ID_Percentile metrics, like I want the data to be below: Note, the id value could be dymanic, so it could be A,B,C in other time or, A,B,C,D etc.

Time        A_p90   A_p95   A_p99   B_p90   B_p95   B_p99
2025-01-06  38      124     156     66      120     130
2025-01-13  55      99      150     77      100     130

May I know how to write the kusto query? thank you.

• kql
• azure-data-explorer

• Please edit your question to provide a minimal reproducible example. As written, you provided only a set of requirements. – David Makogon Commented Mar 20 at 0:16

3 Answers 3

Try with the below query to get the expected output.

let raw = datatable(id:string, Time:string, p90:long, p95:long, p99:long)
[
    "A", "2025-01-06", 38, 124, 156,
    "B", "2025-01-06", 66, 120, 130,
    "A", "2025-01-13", 55, 99, 150, 
    "B", "2025-01-13", 77, 100, 130, 
];
raw
| extend ID_Percentile = strcat(id, "_p", tostring(p90), "_", tostring(p95), "_", tostring(p99))
| summarize 
    A_p90 = maxif(p90, id == "A"),
    A_p95 = maxif(p95, id == "A"),
    A_p99 = maxif(p99, id == "A"),
    B_p90 = maxif(p90, id == "B"),
    B_p95 = maxif(p95, id == "B"),
    B_p99 = maxif(p99, id == "B")
    by Time
| order by Time

Output:

mv-expand + pivot

let raw = datatable(id:string, Time:string, p90:long, p95:long, p99:long)
[
    "A", "2025-01-06", 38, 124, 156,
    "B", "2025-01-06", 66, 120, 130,
    "A", "2025-01-13", 55, 99, 150, 
    "B", "2025-01-13", 77, 100, 130,
    "A", "2025-01-17", 10, 20, 30, 
    "B", "2025-01-17", 40, 50, 60,
    "C", "2025-01-17", 70, 80, 190,
    "B", "2025-01-21", 11, 22, 33,
    "D", "2025-01-21", 44, 55, 66    
];
raw
| mv-expand columnName  = dynamic(["p90", "p95", "p99"]) to typeof(string)
           ,columnValue = pack_array(p90, p95, p99) to typeof(long)
| extend columnName = strcat(id, "_", columnName)
| evaluate pivot(columnName, take_any(columnValue), Time)

bag_pack() + make_bag() + bag_unpack()

let raw = datatable(id:string, Time:string, p90:long, p95:long, p99:long)
[
    "A", "2025-01-06", 38, 124, 156,
    "B", "2025-01-06", 66, 120, 130,
    "A", "2025-01-13", 55, 99, 150, 
    "B", "2025-01-13", 77, 100, 130,
    "A", "2025-01-17", 10, 20, 30, 
    "B", "2025-01-17", 40, 50, 60,
    "C", "2025-01-17", 70, 80, 190,
    "B", "2025-01-21", 11, 22, 33,
    "D", "2025-01-21", 44, 55, 66    
];
raw
| extend bag = bag_pack(strcat(id,"_p90"), p90, strcat(id,"_p95"), p95, strcat(id,"_p99"), p99)
| summarize make_bag(bag) by Time
| evaluate bag_unpack(bag_bag)