Create an aggregate function for GROUP BY?

Question

I have a query that outputs results as follows:

Person   Food
======   ======
Abner    Apple
Beth     Banana
Beth     Peach
Carlos   Grape
Carlos   Kiwi
Carlos   Strawberry
. .      . .

Namely, it associates a person with one or more foods. I'd like the functionality of an aggregate function to choose a food, but none of the built-in aggregates (SUM, MAX, MIN,etc.) really applies to the situation.

If I have a function F() that, given a list of foods, either:

1. provides an ordered list of foods based on some optimization
2. provides the optimal unique choice from the list

is there any way I can apply a GROUP BY onto it directly?

Does anyone have an example of a query that does this some other way, in case this is a pipe dream?

= = = =

EDIT: Let's say that out of the options shown above, the optimal list is {Kiwi, Grape, Banana, Strawberry, Apple, Peach}, then the output should say:

Abner   Apple
Beth    Banana
Carlos  Kiwi

i.e., one record per Person, and the Food for each is the first occurrence in the optimal list of a Food associated to that Person in the original table.

Answer 1

You mean, you want to concatenate those foods?

Try this:

select p.Person, stuff((select ', ' + f.Food [text()] from Food f where f.PersonId = p.Id order by f.Food for xml path('')),1,2,'') [Foods]
from Person p

Answer 2

You could go without group by with this:

SELECT DISTINCT Person, F(Person) AS FoodFunction FROM TableName

You'd have to change the function F() so that it takes person (not food list) and get the food list through:

SELECT Food FROM TableName
WHERE Person = @Person

Answer 3

SELECT DISTINCT t.Person, f.Food FROM MyTable AS t
CROSS APPLY(
SELECT TOP 1 Food FROM MyTable AS t1 WHERE t.Person = t1.Person
ORDER BY <your method of rating and choosing goes here>
) AS f

Answer 4

Please note that in my queries here you can replace the OptimalFood table with your F() rowset-returning function (if I'm understanding correctly).

WITH PersonFood (Name, Food) AS (
   SELECT 'Abner', 'Apple'
   UNION ALL SELECT 'Beth', 'Banana'
   UNION ALL SELECT 'Beth', 'Peach'
   UNION ALL SELECT 'Carlos', 'Grape'
   UNION ALL SELECT 'Carlos', 'Kiwi'
   UNION ALL SELECT 'Carlos', 'Strawberry'
   UNION ALL SELECT 'Delilah', 'Passionfruit'
), OptimalFood (Priority, Food) AS (
   SELECT 1, 'Kiwi'
   UNION ALL SELECT 2, 'Grape'
   UNION ALL SELECT 3, 'Banana'
   UNION ALL SELECT 4, 'Strawberry'
   UNION ALL SELECT 5, 'Apple'
   UNION ALL SELECT 6, 'Peach'
), Choices AS (
   SELECT
      Selector = Row_Number() OVER (PARTITION BY F.Name ORDER BY Coalesce(O.Priority, 2147483647)),
      F.Name,
      Food = Coalesce(O.Food, '<None>')
   FROM
      PersonFood F
      LEFT JOIN OptimalFood O ON F.Food = O.Food
)
SELECT
   Name,
   Food
FROM Choices
WHERE Selector = 1;

If you have a table with all the people listed once already, this may be a better way:

WITH PersonFood (Name, Food) AS (
   SELECT 'Abner', 'Apple'
   UNION ALL SELECT 'Beth', 'Banana'
   UNION ALL SELECT 'Beth', 'Peach'
   UNION ALL SELECT 'Carlos', 'Grape'
   UNION ALL SELECT 'Carlos', 'Kiwi'
   UNION ALL SELECT 'Carlos', 'Strawberry'
   UNION ALL SELECT 'Delilah', 'Passionfruit'
), OptimalFood (Priority, Food) AS (
   SELECT 1, 'Kiwi'
   UNION ALL SELECT 2, 'Grape'
   UNION ALL SELECT 3, 'Banana'
   UNION ALL SELECT 4, 'Strawberry'
   UNION ALL SELECT 5, 'Apple'
   UNION ALL SELECT 6, 'Peach'
), Person AS (
   SELECT DISTINCT Name FROM PersonFood
)
SELECT
   P.Name,
   Food = Coalesce(X.Food, '<None>')
FROM
   Person P
   OUTER APPLY (
      SELECT TOP 1 O.Food
      FROM
         PersonFood F
         INNER JOIN OptimalFood O ON F.Food = O.Food
      WHERE P.Name = F.Name
      ORDER BY O.Priority
   ) X;

I would expect that in all these cases you're actually using numeric keys rather than varchar strings. I would hope. :)