CODEWITHSUNDEEP
mysql
silent-box
silent-box

Reputation: 1666

Complex query from MySQL with GROUP_CONCAT

I'm looking for a way to SELECT this result:

id_item     name1       name2       name3
1           value10     value20     value30
2           -           value40     -
3           value50     value60     -

From this DB table (here's custom fields to each id_item):

id_item     Name    Value
1           name1   value10
1           name2   value20
1           name3   value30
2           name2   value40
3           name1   value50
3           name2   value60

Can't solve it. Is there a way?

Upvotes: 0

Views: 55

Answers (2)

VMai
VMai

Reputation: 10336

You can get your new result with this query:

SELECT
    id_item,
    MAX(CASE WHEN Name = 'name1' THEN value ELSE '-' END) name1,
    MAX(CASE WHEN Name = 'name2' THEN value ELSE '-' END) name2,
    MAX(CASE WHEN Name = 'name3' THEN value ELSE '-' END) name3
FROM
    your_table
GROUP BY
    id_item;

Demo

I adapted this great answer of bluefeet to generate this query dynamically:

SET @sql = NULL;
SELECT 
    GROUP_CONCAT(DISTINCT
      'MAX(CASE WHEN e.Name = ''',
      e.Name,
      ''' THEN value ELSE ''-'' END) AS ',
      e.Name
    ) INTO @sql
FROM example e;

SET @sql = CONCAT('SELECT e.id_item
                    , ', @sql, ' 
                   from example e
                   GROUP BY e.id_item;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Demo

Upvotes: 2

Ronak Shah
Ronak Shah

Reputation: 1549

I think You will need to develop store procedure.

Step 1 : find distinct value of 'Name' Column and store in any session variable

Step 2: use while loop and generate dynamic query

Example:

select * from (select A.id_item,if(A.Name='name1',A.Value,Null) as  'name1',if(A.Name='name2',A.Value,Null) as  'name2',if(A.Name='name3',A.Value,Null) as  'name3' from testtable A) group by id_item

I hope you get my explanation

If you have any query then comment here then I can build example in sql fiddle if needed.

Upvotes: 0

Related Questions