CODEWITHSUNDEEP
mysql
theisenm85
theisenm85

Reputation: 31

How can I combine multiple rows of a table into multiple columns in Mysql with 1 query?

I have a table that stores values based on two identifying values (product code, and a detail code) that make each row unique and a 3rd value that stores a type of value based on the detail code. What I would like to do is display all of the third values for any one product code in new columns. The detail code would act as a column header.

What I have tried so far:

SELECT id1, 
CASE WHEN id2 ='A'
THEN value
ELSE 0 
END A, 
CASE WHEN id2 ='B'
THEN value
ELSE 0 
END B
FROM table1
WHERE id2 = 'A' OR id2 = 'B'
GROUP BY id1

This has worked ok, except that when a value for id2 = 'A' exists inn the table, the CASE WHEN for id2 = 'B' defaults to 0 instead of the correct value, if there is one. If there is no record for the id2 = 'A' then the CASE WHEN will work correctly for id2 = 'B'.

I'm assuming there's also a better way to go about this, but had trouble finding this exact situation anywhere. I can definitely use the data without the multiple columns, but was hoping not to/learn something

Upvotes: 1

Views: 61

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271191

Here is your query formatted so I can read it:

SELECT id1, 
       (CASE WHEN id2 = 'A' THEN value
             ELSE 0 
        END) as A, 
       (CASE WHEN id2 = 'B' THEN value
             ELSE 0 
        END) as B
FROM table1
WHERE id2 in ('A', 'B')
GROUP BY id1;

What I notice is that you have the id2 field in the select with no aggregation function. You seem to want the value when one or the other occurs. Try this:

SELECT id1, 
       max(CASE WHEN id2 = 'A' THEN value
                ELSE 0 
           END) as A, 
       max(CASE WHEN id2 = 'B' THEN value
                ELSE 0 
           END) as B
FROM table1
WHERE id2 in ('A', 'B')
GROUP BY id1;

Upvotes: 1

Related Questions