Rename a field based on the the value of another field?

[the_wolverine]

If I have a table like the following and want to combine the values into a single row for further evaluation, how would I do it?

state,field1, count
ca,abc,200
ca,xyz,100
ca,def,300

I'd like a row that has:
state,abc,xyz,def
ca,200,100,300

[sideview · Accepted solution]

You actually can just tack on

| xyseries state field1 count

Long Version :

This first table of results you've given is what you might call a "stats style" result set. In other words it looks like the output of | stats count by state field1

state,field1, count
ca,abc,200
ca,xyz,100
ca,def,300

And the second table of results you've given looks like a "chart style" result set, ie it looks like the output of | chart count over state by field1

state,abc,xyz,def
ca,200,100,300

In general, the main function fo the xyseries command is to do exactly this - convert a "stats-style" result set into a "chart style" result set, which is why the answer is simply | xyseries state field1 count

[the_wolverine]

One way would be to use eval to apply a field value to an existing field name:

| eval {field1}=count

This will give you:

state,abc,xyz,def
ca,200,,
ca,,100,
ca,,,300

Then do: | stats values(abc) as abc, values(xyz) as xyz, values(def) as def by state

state,abc,xyz,def
ca,200,100,300