How to combine multiple rows into different columns on single row

hpendela — Thu, 01 Oct 2020 22:27:35 GMT

I have a query that returns the following result.

Status	Count
200	800
404	34
400	20
500	12

And I would like to transform it to something like this

Count(200)	Count(404)	Count(400)	Count(500)
800	34	20	12

Is this possible? Thanks.

Re: How to combine multiple rows into different columns on single row

bowesmana — Thu, 01 Oct 2020 23:35:58 GMT

@hpendela

There are a couple of slightly different ways to do this. The first

| makeresults | eval _raw="Status,Count 200,800 404,34 400,20 500,12" | multikv forceheader=1 | table Status, Count | eval Count({Status})=Count | fields - Count | stats values(Count*) as Count*

Take the last 3 lines, which gives exactly your example.

This is slightly different in that it does not rename the fields as you want, but achieves the same goal.

Just the last line will do this

or you can add the next two lines to give the exact same

| fields - Count | foreach * [ rename "<<MATCHSTR>>" as "Count(<<MATCHSTR>>)" ]

Hope this helps

topic Re: How to combine multiple rows into different columns on single row in Splunk Search

How to combine multiple rows into different columns on single row

Re: How to combine multiple rows into different columns on single row