Concatenate certain values according to their keys using case

Question

I want to know how to concatenate values for certain keys. Example data:

id	key	value
1	name	Alex
1	status	single
1	age	21
1	income	20K
1	hight	85
2	name	David
2	status	single
2	age	23
2	income	10K
2	hight	75

I try to concatenate just three keys Alex,21,85 per id.

So I have something similar to that:

select case
    when something then null
    else concat(name.value, age.value, hight.value)
end
from names

So what should be in the concat in order to get this values? I don't need to show the keys but just the values combined Alex,21,85 or David,23,75 each time the values are different for each id so it can't be hardcoded.

Answer 1

If I understand correctly, you have a dynamic list of keys such as name, age, hight and you want to display those values per user.

You need to use aggregation and string_agg:

select id, string_agg(
    "value", ','
) within group (order by charindex(',' + "key" + ',', ',name,age,hight,')) as csv
from t
where "key" in ('name', 'age', 'hight')
group by id

Answer 2

DDL

CREATE TABLE person (
    p_id INT AUTO_INCREMENT PRIMARY KEY,
    p_name VARCHAR(255) NOT NULL
);

INSERT INTO person (p_name) VALUES ("Alex"), ("David"), ("Kate"), ("Lisa"), ("Maya");

The query you need. There are more options available so you might wanna read on GROUP_CONCAT()

SELECT GROUP_CONCAT(p_name SEPARATOR ', ') as "string"
FROM person
WHERE p_id IN (2, 4, 5);

Expected output:

string
David, Lisa, Maya