Trying to concatenate three different rows in mysql-CodePudding

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

CodePudding user response：

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

CodePudding user response：

Try this,

    SELECT `c_id`, CONCAT(GROUP_CONCAT(IF(`key` = 'day', `value`, NULL)),'_',GROUP_CONCAT(IF(`key` = 'month', `value`, NULL)),'_',GROUP_CONCAT(IF(`key` = 'year', `value`, NULL))) as dob
    FROM `consumer_attributes`
    GROUP BY `c_id`

CodePudding user response：

Note: is it select_day or day ? You should change it above query if its different .

You didnt join subqueries with main query with id columns, so it finds more than one rows for each record . It should be ok if the rest is ok :

    SELECT 
dmy.consumer_id,
concat (
max(ifnull(    (SELECT `value` FROM consumer_attributes dd WHERE `key` = 'day' and dd.id = dmy.id) , -1)) ,'_' ,
max(ifnull(        (SELECT `value` FROM consumer_attributes mm WHERE `key` = 'month' and mm.id = dmy.id)  , -1) ), '_' ,
max(ifnull(            (SELECT `value` FROM consumer_attributes yy WHERE `key` = 'year' and yy.id = dmy.id) , -1)) )
FROM consumer_attributes dmy
group by dmy.consumer_id

CodePudding user response：

Another approach (if you have a lot of data you might want to time different answers);

SELECT c_id, CONCAT(d.d, '_', m.m, '_', y.y) dob 
FROM (select c_id, value d FROM consumer_attributes WHERE `key`='day') d 
NATURAL JOIN (select c_id, value m FROM consumer_attributes WHERE `key`='month') m 
NATURAL JOIN (select c_id, value y FROM consumer_attributes WHERE `key`='year') y;