如何通过1个查询将一个表的多行组合成Mysql中的多个列？

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'.

除了当表中存在id2 ='A'的值时,id2 ='B'的CASE WHEN默认为0而不是正确的值(如果有的话)。如果没有id2 ='A'的记录,那么CASE WHEN将对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

我假设还有一个更好的方法来解决这个问题,但无法在任何地方找到这种确切的情况。我绝对可以使用没有多列的数据,但希望不要/学习一些东西

1 个解决方案

#1

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中没有聚合函数的id2字段。当一个或另一个发生时,您似乎想要该值。试试这个:

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;

#1