Trying to concatenate three different rows in mysql

Question

So, I have an attributes table and I'm trying to build a query to concatenate into one column

attributes
------------
id, c_id, key, value
1,  1,    'day', 11
2,  1,    'month', 09
3,  1,    'year', 1999
4,  2,    'day', 14
5,  2,    'month', 11
6,  2,    'year', 2004

And this is the query I wrote,

SELECT 
consumer_id,
CONCAT(
    (SELECT `value` FROM consumer_attributes WHERE `key` = 'select_day'),
    '_',
    CONCAT(
        (SELECT `value` FROM consumer_attributes WHERE `key` = 'select_month'),
        '_',
        CONCAT(
            (SELECT `value` FROM consumer_attributes WHERE `key` = 'select_year'),
            '',
            ''
        )
    )
) AS dob
FROM consumer_attributes 

It throws out

ERROR CODE: 1242 Subquery returns more than 1 row

Can someone help me out?

output I'm trying to achieve

consumer_id, concat
1, 11_09_1999
2, 14_11_2004

Answer 1

SELECT c_id, CONCAT_WS('_',
  (SELECT value FROM consumer_attributes a WHERE `key`='day' AND a.c_id = c.c_id),
  (SELECT value FROM consumer_attributes a WHERE `key`='month' AND a.c_id = c.c_id),
  (SELECT value FROM consumer_attributes a WHERE `key`='year' AND a.c_id = c.c_id)) AS dob
FROM (SELECT DISTINCT c_id FROM consumer_attributes) c;

https://www.db-fiddle.com/f/pB6b5xrgPKCivFWcpQHsyE/14