Trying to concatenate three different rows in mysql

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

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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

Method 2

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`

Method 3

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

Method 4

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;

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating