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
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
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
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