How to transform groups of rows into columns

[Hung_Nguyen]

I am running a query that gives me various percentile metric in different row, and I would like to format them in an easily readable table.  For example, here is the current outcome after I run the below query

index=my_indexer
| stats
p50(startuptime) as "startuptime_p50",
p90(startuptime) as "startuptime_p90",
p99(startuptime) as "startuptime_p99",

p50(render_time) as "render_time_p50",
p90(render_time) as "render_time_p90",
p99(render_time) as "render_time_p99",

p50(foobar_time) as "foobar_time_p50",
p90(foobar_time) as "foobar_time_p90",
p99(foobar_time) as "foobar_time_p99",
| transpose

column                             row1
startuptime_p50         50
startuptime_p70         70
startuptime_p90         90
render_time_p50         51
render_time_p70         72
render_time_p90         93
foobar_time_p50         53
foobar_time_p70         74
foobar_time_p90         95

I would like to format the final table as follow (the column header is optional)

Marker                P50         P70         P90
startup                50            70            90
render                 51            72            93
foobar                 53            74            95

thank you very much for your help

[bowesmana · Accepted solution]

See this example of your data using the chart command to do what you want

| makeresults
| eval _raw="column row1
startuptime_p50 50
startuptime_p70 70
startuptime_p90 90
render_time_p50 51
render_time_p70 72
render_time_p90 93
foobar_time_p50 53
foobar_time_p70 74
foobar_time_p90 95"
| multikv forceheader=1
| table column row1
| rex field=column "_?time_(?<metric>\w+)"
| rex field=column "(?<Marker>[^_]*)_?time.*"
| chart values(row1) over Marker by metric

You can sort as required after this