convert the row value to column

[Susha]

Hi All,

we have a query as below 

(index=abc OR index=def) category= * OR NOT blocked =0 AND NOT blocked =2
|rex field=index "(?<Local_Market>[^cita]\w.*?)_" | stats count(Local_Market) by Local_Market
| rename count(Local_Market) as Total_Blocked | addcoltotals col=t labelfield=Local_Market label="Total Blocked"
| append [search (index=abc OR index=def) blocked =0 | rex field=index "(?<Local_Market>\w.*?)_"
| stats count by Local_Market | rename count as Detected_Count | addcoltotals col=t labelfield=Local_Market label="Total Detected")]

Local_market    total Blocked     total detected
Germany            20
ghana                   80
India                     91
total Blocked   191
Germany                                                 10
Ghana                                                       20
India                                                           10
total detected                                        40

i want data like

Local_Market        Germany   ghana   India   Total
total Blocked         20                80           91      191
Total Detected      10               20            10       40

[ITWhisperer]

| makeresults 
| eval _raw="Local_market,Blocked
Germany,20
Ghana,80
India,91"
| multikv forceheader=1 
| table Local_market Blocked
| addcoltotals col=t labelfield=Local_market label="Total"
| append 
    [| makeresults
    | eval _raw="Local_market,detected
Germany,10
Ghana,20
India,10"
| multikv forceheader=1 
| table Local_market detected
| addcoltotals col=t labelfield=Local_market label="Total"
]
| stats values(*) as * by Local_market
| transpose 0 header_field=Local_market column_name=Local_market

[Susha]

@ITWhisperer  thanks but  it seems given code is only for the 3 data but i have huge list of LM ..

how can we get that ..

i have tried below as well ..but its not working as required 

(index=abc OR index=def) category= * OR NOT blocked =0 AND NOT blocked =2
|rex field=index "(?<Local_Market>[^cita]\w.*?)_" | stats count(Local_Market) by Local_Market
| rename count(Local_Market) as Total_Blocked | addcoltotals col=t labelfield=Local_Market label="Total Blocked" | transpose 0 header_field=Local_market column_name=Local_market)]
| append [search (index=abc OR index=def) blocked =0 | rex field=index "(?<Local_Market>\w.*?)_"
| stats count by Local_Market | rename count as Detected_Count | addcoltotals col=t labelfield=Local_Market label="Total Detected" | transpose 0 header_field=Local_market column_name=Local_market)]

[ITWhisperer · Accepted solution]

(index=abc OR index=def) category= * OR NOT blocked =0 AND NOT blocked =2
|rex field=index "(?<Local_Market>[^cita]\w.*?)_"
| stats count(Local_Market) as Blocked by Local_Market
| addcoltotals col=t labelfield=Local_Market label="Total"
| append [search (index=abc OR index=def) blocked =0 | rex field=index "(?<Local_Market>\w.*?)_"
| stats count as Detected by Local_Market
| addcoltotals col=t labelfield=Local_Market label="Total"]
| stats values(*) as * by Local_Market
| transpose 0 header_field=Local_Market column_name=Local_Market