topic How to rearrange table by values in a column in Splunk Search https://community.splunk.com/t5/Splunk-Search/How-to-rearrange-table-by-values-in-a-column/m-p/333725#M99232 <P>So I have the following data as output statistics from a search:</P> <PRE><CODE>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 </CODE></PRE> <P>How can I rearrange it so that it becomes:</P> <PRE><CODE>User A B C Andy 123 123 123 Bob 123 123 0 Cam 123 123 123 </CODE></PRE> <P>Also, what is this rearranging called?</P> Wed, 13 Sep 2017 13:38:17 GMT andrewhlui 2017-09-13T13:38:17Z How to rearrange table by values in a column https://community.splunk.com/t5/Splunk-Search/How-to-rearrange-table-by-values-in-a-column/m-p/333725#M99232 <P>So I have the following data as output statistics from a search:</P> <PRE><CODE>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 </CODE></PRE> <P>How can I rearrange it so that it becomes:</P> <PRE><CODE>User A B C Andy 123 123 123 Bob 123 123 0 Cam 123 123 123 </CODE></PRE> <P>Also, what is this rearranging called?</P> Wed, 13 Sep 2017 13:38:17 GMT https://community.splunk.com/t5/Splunk-Search/How-to-rearrange-table-by-values-in-a-column/m-p/333725#M99232 andrewhlui 2017-09-13T13:38:17Z Re: How to rearrange table by values in a column https://community.splunk.com/t5/Splunk-Search/How-to-rearrange-table-by-values-in-a-column/m-p/333726#M99233 <P>Never mind, I figured out an answer:</P> <P>... | xyseries User, Group, Number</P> Wed, 13 Sep 2017 13:48:03 GMT https://community.splunk.com/t5/Splunk-Search/How-to-rearrange-table-by-values-in-a-column/m-p/333726#M99233 andrewhlui 2017-09-13T13:48:03Z Re: How to rearrange table by values in a column https://community.splunk.com/t5/Splunk-Search/How-to-rearrange-table-by-values-in-a-column/m-p/333727#M99234 <P>Hi andrewhlui,<BR /> add to your search</P> <PRE><CODE>| chart sum(Number) AS Number over User BY Group </CODE></PRE> <P>If you like you can use different functions instead sum.<BR /> Bye.<BR /> Giuseppe</P> Wed, 13 Sep 2017 13:48:31 GMT https://community.splunk.com/t5/Splunk-Search/How-to-rearrange-table-by-values-in-a-column/m-p/333727#M99234 gcusello 2017-09-13T13:48:31Z Re: How to rearrange table by values in a column https://community.splunk.com/t5/Splunk-Search/How-to-rearrange-table-by-values-in-a-column/m-p/333728#M99235 <P>You are looking for the <CODE>chart</CODE> command...</P> <PRE><CODE>| 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 </CODE></PRE> <P>...and then after you review the above, add this on the end and look at this handy little reverse tool...</P> <PRE><CODE>| untable User Group Number </CODE></PRE> Wed, 13 Sep 2017 13:54:00 GMT https://community.splunk.com/t5/Splunk-Search/How-to-rearrange-table-by-values-in-a-column/m-p/333728#M99235 DalJeanis 2017-09-13T13:54:00Z Re: How to rearrange table by values in a column https://community.splunk.com/t5/Splunk-Search/How-to-rearrange-table-by-values-in-a-column/m-p/333729#M99236 <P>@andrewhliu - yep, <CODE>xyseries</CODE> will work too. Check out the <CODE>untable</CODE> command on the results. </P> <P>We're converting your comment to an answer. Please accept the answer so that the question will show as answered.</P> Wed, 13 Sep 2017 13:58:10 GMT https://community.splunk.com/t5/Splunk-Search/How-to-rearrange-table-by-values-in-a-column/m-p/333729#M99236 DalJeanis 2017-09-13T13:58:10Z Re: How to rearrange table by values in a column https://community.splunk.com/t5/Splunk-Search/How-to-rearrange-table-by-values-in-a-column/m-p/333730#M99237 <P>Hi, </P> <P>There is a command called <CODE>transpose</CODE>.</P> <P><A href="http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Transpose">http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Transpose</A> </P> <P>Try it out with DalJeanis example: </P> <P>| makeresults <BR /> | eval User="Andy Bob Cam", Group="A B C" <BR /> | makemv User <BR /> | mvexpand User <BR /> | makemv Group <BR /> | mvexpand Group <BR /> | Where NOT (Group="C" AND User="Bob") <BR /> | eval Number = 123<BR /> | rename COMMENT as "the above just enters your test data"<BR /> | chart sum(Number) over User by Group<BR /> | transpose</P> Wed, 13 Sep 2017 14:16:46 GMT https://community.splunk.com/t5/Splunk-Search/How-to-rearrange-table-by-values-in-a-column/m-p/333730#M99237 abalogh_splunk 2017-09-13T14:16:46Z