How to create a table with field value as column header, and another field value as the column value?

[qxintuit]

I have data like

1. {
    studentId: 1111
    subject: math
    grade: A
} 

2. {
    studentId: 1111
    subject: history
    grade: A
} 

3. {
    studentId: 2222
    subject: math
    grade: A
} 

4. {
    studentId: 2222
    subject: history
    grade: B
} 

and want to have output as

studentId            math .       history
1111                   A              A
2222                   A              B

I used something like

stats list(subject), list(grade) by studentId

But the result is not what I wanted... Can anyone please help me with an example or something? Thanks a lot!

[niketn · Accepted solution]

@qxintuit, try the following run anywhere search with sample data similar to your question:

| makeresults
| eval _raw="{\"students\":[ 
                {
                    \"studentId\": 1111,
                    \"subject\": \"math\",
                    \"grade\": \"A\"
                },
                {  
                    \"studentId\": 1111,
                    \"subject\": \"history\",
                    \"grade\": \"A\"
                },
                {
                    \"studentId\": 2222,
                    \"subject\": \"math\",
                    \"grade\": \"A\"
                },
                {
                    \"studentId\": 2222,
                    \"subject\": \"history\",
                    \"grade\": \"B\"
                } 
            ]
}"
| spath
| rename students{}.grade as grade
| rename students{}.studentId as studentId
| rename students{}.subject as subject
| eval students=mvzip(studentId,mvzip(subject,grade))
| fields students
| mvexpand students
| eval students=split(students,",")
| eval studentId=mvindex(students,0),subject =mvindex(students,1),grade =mvindex(students,2)
| fields - students _*
| chart last(grade) as grade over studentId by subject

[qxintuit]

@niketnilay, thank you so much!