topic Re: How to join 2 rows to only be 1 row in Splunk Search

How to join 2 rows to only be 1 row

mrccasi — Mon, 30 Oct 2017 05:48:17 GMT

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 🙂

Re: How to join 2 rows to only be 1 row

HiroshiSatoh — Mon, 30 Oct 2017 05:53:38 GMT

Try this!

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

Re: How to join 2 rows to only be 1 row

mrccasi — Mon, 30 Oct 2017 06:04:14 GMT

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,

Re: How to join 2 rows to only be 1 row

hardikJsheth — Mon, 30 Oct 2017 06:38:44 GMT

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.

Re: How to join 2 rows to only be 1 row

HiroshiSatoh — Tue, 29 Sep 2020 16:26:43 GMT

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.