transforming table output

[andersmholmgren]

I have a summary index of stats in hourly buckets. I need to caculate rolled up stats for these.
The hourly stats are count, median duration, max duration.
The rollups are total count, avg median duration, max duration per day (also need for month etc)

The end result looks like

txnType   metricName                result
A         Volume                    356
A         AverageMedianDuration     300
A         MaximumDuration           400
B         Volume                    1
B         AverageMedianDuration     902
B         MaximumDuration           1200
C         Volume                    292
C         AverageMedianDuration     1000
C         MaximumDuration           2000
D         Volume                    9439
D         Average Median duration   3000
D         Max Duration              4000

Currently I have been doing separate queries for each metric (i.e. 3 queries). But as they all roll up over the same period I'm hoping to reduce that to one query. The start of the query looks like

.... | stats sum(Volume) as Volume, avg(MedianDuration) as AverageMedianDuration, max(MaximumDuration) as MaximumDuration by txnType

Which gives a result like

txnType Volume  AverageMedianDuration   MaximumDuration
A       356     300                     400
B       1       902                     1200
C       292     1000                    2000
D       9439    3000                    4000

Any ideas on how to transform this into the desired format? I guess this is like an unpivot

[MartinHarper]

I had a similar requirement and I solved it with splunk fieldsummary:
http://docs.splunk.com/Documentation/Splunk/5.0/SearchReference/Fieldsummary

This means you need one query for each transaction type, as opposed to one query for each metric type. That might or might not be better, depending on your data. I was lucky and had a single transaction type and 100s of metrics.

Still looking for a proper solution.

[BobM]

Not sure exactly what you want but have you looked at the transpose command?

[andersmholmgren]

yes but its not quite what I want. That just swaps the columns and rows. i.e. like

txnType A B C D
Volume 356 etc