Splunk Search

How to join 2 rows to only be 1 row

mrccasi
Explorer

Hi, How can I turn multiple rows into a single row? For example,

Name   Skill1  Skill2 Skill3
Shine Oracle
Shine          Java
Shine                 C#

and result should be like this,

Name  Skill1 Skill2 Skill3
Shine Oracle Java   C#

Thank you in advance 🙂

0 Karma

HiroshiSatoh
Champion

Try this!

(your search)|stats first(*) as * by Name

mrccasi
Explorer

hi hiroshisatoh, other skills are missing when i tried using stats first. i also used values and list but the results are not what i am expecting. can you suggest other? thank you,

0 Karma

hardikJsheth
Motivator

I think if you want only four columns in your result, just reframe the search from @HiroshiSatoh to

index=main sourcetype=skill | stats values(skill1) as skill1,values(skill2) as skill2, values(skill3) as skill3 by Name

This will return you the results as expected.

HiroshiSatoh
Champion

Thank you hardikJsheth for explanation.

If you use wildcards(*), all fields are eligible.
This setting is also possible.

ex.

first(skill*) as skill* by Name

Only the "first" one is displayed for first.
"value" displays all values as multiline entries.

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.