How do you put a single row of key value pairs into one line from multiple rows?

[lylereger]

I have looked at various answers, but don't seem to be grasping what seems like it should be easy enough to do. Some help would be appreciated. Here is my set up.

I have data that comes back in a few simple fields(from JSON originally):

id      name      category     catval
123     bob       math         89
123     bob       English      76
123     bob       science      94
124     joe       math         79
124     joe       English      83
124     joe       science      91

And the output I am looking for is:

 id      name      math     English   science
123      bob       89       76        94
124      joe       79       83        91

Can anyone point me in the right direction here? If it helps, I do not have to dynamically determine the category field. I will know those values. I do need to grab them and get them sorted into single rows though.

[Vijeta]

Hi,

You can try something like below-

  <yourquery>|  eval id=id.",".name
    |chart sum(catval) as val over id by  catgory|  rex field=id "\w+,(?<name>\w+)"
| rex field=id "(?<id>\w+),"

|  table id name *