I am modeling some abstract flow tests.

A 'flow instance' is defined as some sort of flow from Node A to Node B. This is simply modeled with 3 tables as follows (I have left out the alter table add constraints for brevity):

CREATE TABLE FLOW
(
  ID integer NOT NULL PRIMARY KEY,
  NAME varchar(20),
);

CREATE TABLE NODE
(
  ID integer NOT NULL PRIMARY KEY,
  NAME varchar(20),
);

CREATE TABLE J_FLOW_INSTANCE
(
  ID integer NOT NULL PRIMARY KEY,
  FLOW integer, -- FOREIGN KEY (FLOW) REFERENCES FLOW (ID)
  SRC integer, -- FOREIGN KEY (SRC) REFERENCES NODE (ID)
  DEST integer -- FOREIGN KEY (DEST) REFERENCES NODE (ID)
);

Each test represents a flow instance with a test mode:

CREATE TABLE MODE
(
  ID integer NOT NULL PRIMARY KEY,
  NAME varchar(20),
);

CREATE TABLE J_FLOW_TEST
(
  ID integer NOT NULL PRIMARY KEY,
  INSTANCE integer, -- FOREIGN KEY (INSTANCE) REFERENCES J_FLOW_INSTANCE (ID)
  MODE integer -- FOREIGN KEY (MODE) REFERENCES MODE (ID)
);

The results of each test are measured in terms of multiple characteristics, i.e. High Flow, Low Loss or High Flow, High Loss etc.

CREATE TABLE CHARACTERISTIC
(
  ID integer NOT NULL PRIMARY KEY,
  NAME varchar(20)
);

CREATE TABLE J_TEST_RESULT_CHARS
(
  TEST integer, -- FOREIGN KEY (TEST) REFERENCES J_FLOW_TEST (ID)
  RESULT integer -- FOREIGN KEY (RESULT) REFERENCES CHARACTERISTIC (ID)
);

With characteristics being defined by multiple properties:

CREATE TABLE PROPERTY
(
  ID integer NOT NULL PRIMARY KEY,
  NAME varchar(20)
);

CREATE TABLE J_CHAR_PROPERTY
(
  CHARACTERISTIC integer, -- FOREIGN KEY (CHARACTERISTIC) REFERENCES CHARACTERISTIC (ID)
  PROPERTY integer -- FOREIGN KEY (PROPERTY) REFERENCES PROPERTY (ID)
);

I can query the results perfectly using the following query:

select jft.ID as TEST_ID, JFI_ID, F_NAME, N1_NAME, N2_NAME, m.NAME as MODE, rc.NAME as CHARACTERISTIC, p.NAME as PROPERTY
from J_FLOW_TEST jft
join
(
    select jfi.ID as JFI_ID, f.NAME as F_NAME, n1.NAME as N1_NAME, n2.NAME as N2_NAME
    from J_FLOW_INSTANCE jfi
    join FLOW f on f.ID = FLOW
    join NODE n1 on n1.ID = SRC
    join NODE n2 on n2.ID = DEST
)
on JFI_ID = jft.INSTANCE
join MODE m on m.ID = jft.MODE
join J_TEST_RESULT_CHARS jtrc on jtrc.TEST = jft.ID
join CHARACTERISTIC rc on rc.ID = jtrc.RESULT
join J_CHAR_PROPERTY jcp on jcp.CHARACTERISTIC = rc.ID
join PROPERTY p on p.ID = jcp.PROPERTY
order by TEST_ID, F_NAME, N1_NAME, N2_NAME, MODE, CHARACTERISTIC, PROPERTY

Which gives me results like this:

TEST_ID    JFI_ID    F_NAME    N1_NAME    N2_NAME    MODE    CHARACTERISTIC    PROPERTY
1    1    FLOW_1    NODE_A    NODE_B    MODE_1    HIGH FLOW   

> 10 litres  / sec

1    1    FLOW_1    NODE_A    NODE_B    MODE_1    HIGH FLOW   

> 50 gals / hour

1    1    FLOW_1    NODE_A    NODE_B    MODE_1    LOW LOSS    Pump current < 30
2    2    FLOW_2    NODE_C    NODE_D    MODE_1    LOW FLOW    < 5 litres / sec
44    3    FLOW_1    NODE_C    NODE_D    MODE_2    LOW FLOW    < 5 litres / sec

I have 2 questions:

Q1. How can I group and LIST() to produce a single row for the first test case (FLOW_1 from NODE_A to NODE_B with MODE_1). The result would look something like this:

1    1    FLOW_1    NODE_A    NODE_B    MODE_1    HIGH_FLOW, LOW_LOSS    > 10 litres / sec, > 50 gals / hour, Pump current < 30

Q2. How can I create a further table J_RESULT_GROUP and query for arbitrarily grouping results together where any distinct elements are LIST()ed whilst common elements are 'GROUP'ed (i.e. appear only once). For example if J_RESULT_GROUP had 2 rows:

GROUP TEST
1    2
1    44

It would give:

TEST_ID    JFI_ID    F_NAME    N1_NAME    N2_NAME    MODE    CHARACTERISTIC    PROPERTY
2,44    2,3    FLOW_1, FLOW_2    NODE_C    NODE_D    MODE_1, MODE_2    LOW_FLOW    < 5 litres / sec

I am sorry for the long question, but I have struggled to phrase it in simple terms.

TIA for any help!