Your solutions would work if my event contained a few fields. However, the data includes 20+ fields [Loc, FN, LN, Address, City, State, Zip, Phone, ID, etc.]; and will be increasing to over 100 fields. If we mvappend all of these fields, this would be extremely inefficient, as well as make sorting/searching on a specific field(s) very cumbersome.
A colleague suggested using list instead of values: | stats list(FirstName), list(LastName) BY Loc. However, I don't believe Splunk would handle event data where a field was null or blank properly. It would not enter a blank line in the results table.
| eval Name=mvzip('LastName','FirstName',", ")
| stats values(Name) as Name by Loc
That gives you something like:
Collins, Phil Lennon, John
Jones, Tom McCartney, Paul
I use this kind of thing with a DHCP lookup table I build so that the IP to NIC/MAC to hostname to timestamp relationship is preserved. Otherwise if you just did stats values IP values MAC by hostname you wouldn't know which IP went with which MAC address. And if you want to preserve a reference history you also need to link these assignments to the date/time they were assigned.