How to rearrange table by values in a column

[andrewhlui]

So I have the following data as output statistics from a search:

User      Group         Number
Andy          A            123
Andy          B            123
Andy          C            123
Bob           A            123
Bob           B            123
Cam           A            123
Cam           B            123
Cam           C            123

How can I rearrange it so that it becomes:

User         A         B        C
Andy       123       123      123
Bob        123       123        0
Cam        123       123      123

Also, what is this rearranging called?

[abalogh_splunk]

Hi,

There is a command called transpose.

http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Transpose

Try it out with DalJeanis example:

| makeresults
| eval User="Andy Bob Cam", Group="A B C"
| makemv User
| mvexpand User
| makemv Group
| mvexpand Group
| Where NOT (Group="C" AND User="Bob")
| eval Number = 123
| rename COMMENT as "the above just enters your test data"
| chart sum(Number) over User by Group
| transpose

[DalJeanis]

You are looking for the chart command...

| makeresults 
| eval User="Andy Bob Cam", Group="A B C" 
| makemv User 
| mvexpand User 
| makemv Group 
| mvexpand Group 
| Where NOT (Group="C" AND User="Bob") 
| eval Number = 123
| rename COMMENT as "the above just enters your test data"

| chart sum(Number) over User by Group

...and then after you review the above, add this on the end and look at this handy little reverse tool...

| untable User Group Number

[gcusello]

Hi andrewhlui,
add to your search

| chart sum(Number) AS Number over User BY Group

If you like you can use different functions instead sum.
Bye.
Giuseppe

[andrewhlui · Accepted solution]

Never mind, I figured out an answer:

... | xyseries User, Group, Number

[DalJeanis]

@andrewhliu - yep, xyseries will work too. Check out the untable command on the results.

We're converting your comment to an answer. Please accept the answer so that the question will show as answered.