How to collate each consecutive N rows into one row with N columns?

[bhawkins1]

I have a search which produces a stats with 2 columns, and n = 3k, where k is an integer, rows. The second column is a simple integer, and the rows form logical triplets, like so:

foo_x  2
foo_y  2
foo_z  5
bar_x 12
bar_y  5
bar_z  9
...

How can I transform my result into a form with k rows and 3+1 columns, like so?:

        x   y   z
foo     2   2   5
bar    12   5   9

Normally I would achieve this by changing the form of the query that produces the n rows into something more sensible. In this case, I'm not sure if that's possible - read on for why -

Extra information regarding source of these 3k rows

I have a list of users, and k properties of those users, like so:

             fishs_age    dogs_age    cats_age    babys_age
user_a
user_b
user_c
...

For each property, I classify 3 risk labels (they need to be separate so I can later sum them):

| foreach fishs_age,
          dogs_age,
          cats_age  [eval <<FIELD>>_hi = if(<<FIELD>> >= 9, 1, 0) ,
                          <<FIELD>>_md = if(<<FIELD>> >= 4 AND <<FIELD>> < 9, 1, 0) ,
                          <<FIELD>>_lo = if(<<FIELD>> >= 2 AND <<FIELD>> < 4, 1, 0)]

I then calculate sums and flip the axes in hopes to make a chart:

| stats sum(*_lo), sum(*_md), sum(*_hi)
| rename sum(*) as *
| transpose

And this is the source of the original dataset

foo_x  2
foo_y  2
foo_z  5
bar_x 12
bar_y  5
bar_z  9
...

i.e.

fishs_age_hi    2
fishs_age_md    5
fishs_age_lo    6
dogs_age_hi     3
dogs_age_md     4
dogs_age_lo    12
...

Again: what I want is to transform this into:

        x   y   z
foo     2   2   5
bar    12   5   9

i.e.

             hi  md  lo
fishs_age     2   2   5
dogs_age     12   5   9

[gokadroid · Accepted solution]

Assuming this is how your original data showed up in respective fields myField and value:

myField     value
dogs_age_lo 12
dogs_age_md 4
dogs_age_hi 3
fishs_age_lo   6
fishs_age_md   5
fishs_age_hi   2

Then try this:

your query to return myField and value
| rex field=myField "(?<fieldName>.*)_(?<columnName>[\S]+)"
| chart values(value) over fieldName by columnName

[bhawkins1]

That's done it - thanks a bunch! I would have never found this functionality without your help.