0

Very similar to my last question, now I want only the, "full combination," for a group in order of priority. So, from this source table:

+-------+-------+----------+
| GROUP | State | Priority |
+-------+-------+----------+
|   1   |  MI   |     1    |
|   1   |  IA   |     2    |
|   1   |  CA   |     3    |
|   1   |  ND   |     4    |
|   1   |  AZ   |     5    |
|   2   |  IA   |     2    |
|   2   |  NJ   |     1    |
|   2   |  NH   |     3    |

And so on...

I need a query that returns:

+-------+---------------------+
| GROUP |     COMBINATION     |
+-------+---------------------+
|   1   | MI, IA, CA, ND, AZ  |
|   2   | NJ, IA, NH          |
+-------+---------------------+

Thanks for the help, again!

Community
  • 1
  • 1
anonimitie
  • 39
  • 9

1 Answers1

1

Use listagg() ordering by priority within the group.

SELECT "GROUP",
       listagg("STATE", ', ') WITHIN GROUP (ORDER BY "PRIORITY")
       FROM "ELBAT"
       GROUP BY "GROUP";

db<>fiddle

sticky bit
  • 36,626
  • 12
  • 31
  • 42