I have a 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...
How do I write a query that makes all the sets of the states by group, in priority order? Like so:
+-------+--------------------+
| GROUP | SET |
+-------+--------------------+
| 1 | MI |
| 1 | MI, IA |
| 1 | MI, IA, CA |
| 1 | MI, IA, CA, ND |
| 1 | MI, IA, CA, ND, AZ |
| 2 | NJ |
| 2 | NJ, IA |
| 2 | NJ, IA, NH |
+-------+--------------------+
This is similar to my question here and I've tried to modify that solution but, I'm just a forty watt bulb and it's a sixty watt problem...
This problem actually looks simpler than the answer to the question you linked, which is an excellent solution to that problem. Nevertheless, this uses the same hierarchical queries, with connect by
If it is the case that priority is always a continuous sequence of numbers, this will work
SELECT t.grp, level, ltrim(SYS_CONNECT_BY_PATH(state,','),',') as "set"
from t
start with priority = 1
connect by priority = prior priority + 1
and grp = prior grp
However, if that's not always true, we would require row_number() to define the sequence based on the order of priority ( which need not be consecutive integer)
with t2 AS
(
select t.*, row_number()
over ( partition by grp order by priority) as rn from t
)
SELECT t2.grp, ltrim(SYS_CONNECT_BY_PATH(state,','),',') as "set"
from t2
start with priority = 1
connect by rn = prior rn + 1
and grp = prior grp