如何在每个组中输出一个项目所属的列? [英] How to output a column per group that an item is a member of?

问题描述

给出此SQL输入表

GroupId   Item
1         Fish
1         FishBowl
2         Fish
3         Fish

我如何获得此输出?

Item     IsInGroup1   IsInGroup2   IsInGroup3
Fish     Y            Y            Y
FishBowl Y            N            N

请注意，组数可以更改

推荐答案

通常，您无法使用标准SQL进行可变数量的组.您必须事先了解小组.这是因为任何SQL查询都必须知道列数及其名称.

In general, you can't do a variable number of groups with standard SQL. You have to know the groups beforehand. This is because any SQL query must know the number of columns, and their names.

SELECT Item, 
  MAX(CASE GroupId WHEN 1 THEN 'Y' ELSE 'N' END) AS IsInGroup1, 
  MAX(CASE GroupId WHEN 2 THEN 'Y' ELSE 'N' END) AS IsInGroup2, 
  MAX(CASE GroupId WHEN 3 THEN 'Y' ELSE 'N' END) AS IsInGroup3
FROM ThisInputTable
GROUP BY Item;

Microsoft SQL Server确实具有一些用于PIVOT表的功能，但是这不是标准的SQL.我不是Microsoft用户，所以我为您提供指向""，其余部分留给您.

Microsoft SQL Server does have some facility for PIVOT tables, however this is not standard SQL. I'm not a Microsoft user so I'll give you a link to "Using PIVOT and UNPIVOT," and leave the rest to you.

这篇关于如何在每个组中输出一个项目所属的列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！