Multiple Rows into one row with primary key

[jimhill]

Hi, 

I have data that looks like this (as you can see user_id 9 has filled numerous rows). This is just a csv ingested and being searched via lookup

user_id	meta_key	meta_value
9	nickname	341
9	first_name	Gilda
9	last_name	Lilia
9	description
9	rich_editing	TRUE
9	syntax_highlighting	TRUE
9	comment_shortcuts	FALSE
9	bz_last_active	202024300
9	_sd_last_login	2251532
9	_jackqueline_persistent_cart_1	a:1:{s:4:"cart";a:0:{}}
9	_order_count	0
9	new_users_id	XM00360
9	antonetta	a:0:{}
9	rank_on_departure	TAD 0fr Class
9	phone_number	12003601
9	add_love	1/120 CARSON ROAD
9	last_name_01	Lashawnda
9	christina_code	1100
9	last_name_01_05	Wendolyn
9	birth_date	05/00/0451
9	country	Stephania
9	join_date	13/02/2003
9	Date_left	1/05/2010
9	full_name	gilda lilia
9	email	gilda_lilia@outlook.com

I really want the output to look like this, where the items in red, come on a single row linked to the unique user_id. At this stage i have over 3600 unique users, and on average, there are about 40 rows per user.

user_id	first_name	last_name	new_users_id	rank_on_departure	add_love	last_name_01	christina_code	last_name_01_05	country	email
9	Gilda	Lilia	XM00360	TAD 0fr Class	1/120 CARSON ROAD	Lashawnda	1100	Wendolyn	Stephania	gilda_lilia@outlook.com

[ITWhisperer]

| makeresults 
| eval _raw="user_id	meta_key	meta_value
9	nickname	341
9	first_name	Gilda
9	last_name	Lilia
9	description	 
9	rich_editing	TRUE
9	syntax_highlighting	TRUE
9	comment_shortcuts	FALSE
9	bz_last_active	202024300
9	_sd_last_login	2251532
9	_jackqueline_persistent_cart_1	a:1:{s:4:\"cart\";a:0:{}}
9	_order_count	0
9	new_users_id	XM00360
9	antonetta	a:0:{}
9	rank_on_departure	TAD 0fr Class
9	phone_number	12003601
9	add_love	1/120 CARSON ROAD
9	last_name_01	Lashawnda
9	christina_code	1100
9	last_name_01_05	Wendolyn
9	birth_date	05/00/0451
9	country	Stephania
9	join_date	13/02/2003
9	Date_left	1/05/2010
9	full_name	gilda lilia
9	email	gilda_lilia@outlook.com"
| multikv forceheader=1
| fields - linecount



| where match(meta_key, "first_name|last_name|new_users_id|rank_on_departure|add_love|last_name_01|christina_code|last_name_01_05|country|email")
| eval {meta_key}=meta_value
| fields - meta_key meta_value _raw
| stats values(*) as * by user_id

[jimhill]

Thank you so much. Thats perfect. 

Now how do I make it so that I can replicate it for all other users and bring it back in one spreadsheet. Is this achievable?

Thanks again

[ITWhisperer · Accepted solution]

The first part (before the blank lines) just sets up your example data - the piece after the blank lines does the work - notice the by user_id on the stats command, this repeats the process for all your users.

[jimhill]

Thank you so much. Extremely helpful