sql - 一种在 SQL 中透视/堆叠表的方法

Question

我有一张像这样的桌子

garden_1   garden_2   garden_3
apple      grape      lime
orange     apple      kiwi
lime       kiwi       orange

对于整个表中的每个唯一元素，我需要另一列包含所有这些唯一值。

如果该值出现在列上，则应返回 1，否则应返回 0。

所以预期的输出应该是：

fruit    garden_1   garden_2   garden_3
apple       1          1          0
orange      1          0          1
lime        1          0          1
grape       0          1          0
kiwi        0          1          1

在 Python 中，我使用以下方法使其工作：

gardens_new.loc[:, :] = gardens.stack().reset_index().pivot_table(index=0, columns="level_1", aggfunc="count").notna().astype(int).droplevel(0, axis=1)

关于如何在 SQL 中做同样的事情的任何想法？

标签： sqlstackpivot-table

Answer 1

您可以取消透视和聚合。对于 unpivoting，最通用的方法是union all：

select fruit,
       sum(garden_1) as garden_1,
       sum(garden_2) as garden_2,
       sum(garden_3) as garden_3
from ((select garden_1 as fruit, 1 as garden_1, 0 as garden_2, 0 as garden_3
       from t
      ) union all
      (select garden_2, 0 as garden_1, 1 as garden_2, 0 as garden_3
       from t
      ) union all
      (select garden_3, 0 as garden_1, 0 as garden_2, 1 as garden_3
       from t
      )
     ) g
group by fruit;