How to transpose rows of one column into multiple columns?

[cjkar]

I currently have multiple entries in the VALUES column for each host.

The table currently looks like: 

hostname	VALUES
HOST1	ENV1 APP1 LOC1
HOST2	ENV2 APP2 LOC2

I would like the table to read as:

hostname	ENV	APP	LOC
HOST1	ENV1	APP1	LOC1
HOST2	ENV2	APP2	LOC2

I am essentially trying to transpose the column "VALUE" and create 3 separate columns with the custom headings "ENV,APP and LOC" 

[kamlesh_vaghela · Accepted solution]

@cjkar 

I think simple eval can help you on this. Can you please try this with your search?

YOUR_SEARCH | eval ENV=mvindex(VALUES,0),APP=mvindex(VALUES,2),LOC=mvindex(VALUES,2)

My Sample Search :

| makeresults 
| eval _raw="hostname,VALUES
HOST1,ENV1|APP1|LOC1
HOST2,ENV2|APP2|LOC2" | multikv forceheader=1 | eval VALUES=split(VALUES,"|")
| rename comment as "Upto now is for sample data only"
| table hostname,VALUES | eval ENV=mvindex(VALUES,0),APP=mvindex(VALUES,2),LOC=mvindex(VALUES,2)

 KV

[cjkar]

Worked perfectly thanks @kamlesh_vaghela !