dbquery - create a field using a field value

[matansocher]

Hi,

I am using sql query with dbquery to get data of an item from 2 different tables.
In the first table I have the Item table that has the most data on it.
the second table:

I would like to join the tables and to get the CUSTOM_FIELD_NAME field as a field and the STRINGVALUE field as the value.
for example, I have an item with ItemID=30, and in the second table I am having 3 record with the ItemID=30:
Customer Name=ENG GEN
Area=Backup
Location=Haifa

and I need the table after the join between the 2 tables to look like that:

ItemID Item Name Customer Name Area Location
30 aaa ENG GEN Backup Haifa
40 bbb IT Backup Haifa
50 ccc IT Windows Tel Aviv

How do I join the tables with the ability to have the fields like that?

Thanks

[woodcock]

In SPL after it is pulled in, like this:

Your Base Search Here
| streamstats count AS serial
| eval KVP=mvzip(CUSTOM_FIELD_NAME,  STRINGVALUE, "=")
| mvexpand KVP
| rex field=KVP "^(?<KVP_key>[^=]+)=(?<KVP_value>[^=]+)$"
| eval {KVP_key}=KVP_value
| fields - KVP* 
| rename COMMENT AS "If you need to reconstitute original events, then add in the next line (or similar)"
| rename COMMENT AS "| stats values(_time) AS _time values(*) AS * BY serial"