How to rotate a table using transpose, remove the first row, and rename the column headers?

[HattrickNZ]

i have this search which gives me:
... | stats max(field1) as foo max(field2) as bar max(field3) as la by subname2
which gives me this:

subname2    foo     bar     la
SG  300000  160000  100000
US  300000  160000  60000 

If i use transpose with this
... | stats max(field1) as foo max(field2) as bar max(field3) as la by subname2 | transpose
I get

column  row 1   row 2
subname2    SG  US
foo     300000  300000
bar     160000  160000
la  100000  60000 

Now how do I remove the the 1st row of the table "subname2 SG US" and use this as my column headers?

I know I can do this:
...| rename column as subname2 | rename "row 1" as SG | rename "row 2" as US
to rename the column headers.

But how do I remove the first row? And is this the best way of doing this?

Ultimately I want this:

subname2    SG  US
foo     300000  300000
bar     160000  160000
la  100000  60000 

NOTE: A smilar question has been asked here

[tmurata_splunk]

I think this is easier.

... 
 | transpose header_field=subname2
 | rename column as subname2

[dhirendra761]

Right this one easier and quickly too...!! thanks 🙂

[ksubramanian198]

Hi , Please try the below and let me know the result
| stats max(field1) as foo max(field2) as bar max(field3) as la by subname2
| transpose header_field=column
| fields- column

[acharlieh · Accepted solution]

Is using transpose a requirement? What about using a combination of untable and xyseries?

Such as:

... | stats max(field1) as foo max(field2) as bar max(field3) as la by subname2 | untable subname2 field value | xyseries field subname2 value | rename field as subname2

Edit: actually one tweak to change the first column name back 🙂

[bhawkins1]

transpose should have an optional parameter over which just does | untable <over> a b | xyseries a <over> b

[HattrickNZ]

tks, I think this is exactly what I am looking for.

transpose was the only way I could think of at the time.