How to output a column per group that an item is a member of?

Question

Given this SQL Input Table

GroupId   Item
1         Fish
1         FishBowl
2         Fish
3         Fish

How Can i derive this Output?

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

Please note that the Number of Groups can be variable

Answer 1

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

Answer 2

Well, I suppose it depends on which database you are using. This works for me:

SELECT Item, IF(GroupId=1,'Y','N') As IsInGroup1, IF(GroupId=2,'Y','N') As IsInGroup2, IF(GroupId=3,'Y','N') As IsInGroup3 FROM New Table GROUP BY Item ;

Answer 3

One of the most frequently asked questions on SO.

Basically, you can use dynamic SQL to build a query of the form Bill Karwin gives by determining the number of groups and a name for each group. Such a query you generate can be ANSI SQL. You can also generate proprietary syntax for SQL Server 2008 (2005 and above)'s PIVOT operator which Bill mentioned.

My example at the first link should be pretty clear.