我想在sql中将分类值拆分为不同的字段

20jt8wwn 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(299)

数据是这样的-州，地区，类别（只有两个字段-清除和怀疑）。我想得到格式的数据（表2）
表-1

State,DC,     cat2
ASSAM KAMRUP CLEAR
ASSAM KAMRUP CLEAR
ASSAM KAMRUP SUSPECT
ASSAM KAMRUP CLEAR
ASSAM Cachar CLEAR
ASSAM Cachar CLEAR
BIHAR Buxar  SUSPECT
BIHAR Buxar  CLEAR
BIHAR Buxar  CLEAR
BIHAR Buxar  SUSPECT
BIHAR Buxar  CLEAR
BIHAR Buxar  SUSPECT
BIHAR Buxar  CLEAR

我想要这样的东西-表2

State DC     CLEAR        SUSPECT
ASSAM KAMRUP count(CLEAR) count(SUSPECT)
ASSAM Cachar count(CLEAR) count(SUSPECT)
BIHAR BUXAR  count(CLEAR) count(SUSPECT)

sql postgresql pivot

来源：https://stackoverflow.com/questions/62016354/i-want-to-split-categorial-value-into-different-fields-in-sql

2条答案

按热度按时间

f4t66c6m1#

在postgres中，您可以使用过滤聚合：

select state, dc, 
       count(*) filter (where cat2 = 'CLEAR') as clear, 
       count(*) filter (where cat2 = 'SUSPECT') as suspect
from the_table
group by state, dc;

赞(0）回复(0）举报 2021-07-26

yfjy0ee72#

你可以用 sum 与 case 声明也是如此

select
  state,
  DC,
  sum(case when cat2 = 'CLEAR' then 1 else 0 end) as clear,
  sum(case when cat2 = 'SUSPECT' then 1 else 0 end) as suspect
from yourTable
group by 
  state,
  DC

赞(0）回复(0）举报 2021-07-26

我来回答

我想在sql中将分类值拆分为不同的字段

2条答案

相关问题

热门标签

最新问答