如何在SQL中在一列中获取值并使这些值成为其他列 [英] How to take values in one column and have those values be additional columns instead, in SQL

问题描述

说我有一个名为水果的列，它具有以下三个值：

Say I have a column called 'Fruit' and it has these three values:

 ID   | Fruit  |
 010  | Apple  |
 020  | Orange |
 010  | Pear   |

说还有其他列，例如Profile_ID。我如何使该表改为这样读取，即该列中的值现在是其自己的列，并且当配置文件与给定的水果相关联时，它将在该行中显示 X：

Say there are other columns like Profile_ID. How do I get the table to read like this instead, where the values in that one column are now columns of their own, and when a Profile is associated with a given fruit, it'll show a 'X' in that row:

ID   |  Apple  | Orange  | Pear
010  |   x     |         |   x 
020  |         |    x    |    

推荐答案

您可以使用条件聚合。如果您想要一个特定的配置文件ID，则：

You can use conditional aggregation. If you want a particular profile id then:

select id,
       max(case when fruit = 'Apple' and profile_id = ? then 'X' end) as apple,
       max(case when fruit = 'Orange' and profile_id = ? then 'X' end) as orange,
       max(case when fruit = 'Pear' and profile_id = ? then 'X' end) as pear
from t
group by id;

如果您只想知道行是否存在：

If you just want to know if a row exists:

select id,
       max(case when fruit = 'Apple' then 'X' end) as apple,
       max(case when fruit = 'Orange' then 'X' end) as orange,
       max(case when fruit = 'Pear' then 'X' end) as pear
from t
group by id;

这篇关于如何在SQL中在一列中获取值并使这些值成为其他列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！