Concatenate certain values according to their keys using case-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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