Transforming tables without hacky chart(first) usage

[doweaver]

I have some data I'm trying to rearrange into an appropriate table for visualization. It starts out like this:

  Group Subgroup    Value
    1   A   100
    1   B   300
    2   A   500
    2   B   700
    3   A   1000
    3   B   2000

I want to transform it to look like this:

    1   2   3
A   100 500 1000
B   300 700 2000

(Where the column headers are the group, and the rows are the subgroups)

I can accomplish this by calling:

chart first(Value) BY Subgroup, Group

...but that doesn't seem like the right approach. i'm calling an aggregation method when I'm not actually DOING any aggregating, just transforming. Is there a better way to handle this?

[cmerriman · Accepted solution]

try adding this instead of the chart:

|xyseries Subgroup Group Value

it worked for me with this:

 | makeresults |eval data="group=1 subgroup=a value=100, group=1 subgroup=b value=300,group=2 subgroup=a value=500,group=2 subgroup=b value=700"|makemv data delim=","|mvexpand data|eval _raw=data|kv|table group subgroup value|xyseries subgroup group value

[doweaver]

Thank you! Exactly what I was looking for!