Mike,

> 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    CHARACTERIST
> IC    PROPERTY
> 2,44    2,3    FLOW_1, FLOW_2    NODE_C    NODE_D    MODE_1,
> MODE_2    LOW_FLOW    < 5 litres / sec

Look at using Common Table Expressions (CTE)

Here is a rough sample based on your details

WITH JFT_JRG (N1_ID, N2_ID, Group) AS (
SELECT
SRC as N1_ID, DEST as N2_ID, jrg.Group, LIST( Test) as TestList
FROM J_FLOW_TEST jft
JOIN J_FLOW_INSTANCE jfi ON jfi.ID = jft.INSTANCE
JOIN J_Result_Group jrg ON jrg.Test = jft.ID
GROUP BY 1, 2, 3
)
WITH JFT_JFI (N1_ID, N2_ID, Group) AS (
SELECT
SRC as N1_ID, DEST as N2_ID, jrg.Group, LIST( jfi.ID) as JFIList
FROM J_FLOW_TEST jft
JOIN J_FLOW_INSTANCE jfi ON jfi.ID = jft.INSTANCE
JOIN J_Result_Group jrg ON jrg.Test = jft.ID
GROUP BY 1, 2, 3
)
SELECT
Jft_JRG.TestList,
Jft_JFI.JFIList,
...
FROM (
SELECT
DISTINCT SRC, DEST, jrg.Group
FROM J_FLOW_TEST jft
JOIN J_FLOW_INSTANCE jfi ON jfi.ID = jft.INSTANCE
JOIN J_Result_Group jrg ON jrg.Test = jft.ID
) jft_Set
JOIN NODE n1 ON n1.ID = jft_Set.SRC
JOIN NODE n2 ON n2.ID = jft_Set.DEST
JOIN JFT_JRG ON N1_ID = jft_Set.SRC AND N2_ID = jft_Set.DEST AND Group = jft_Set.Group
JOIN JFT_JFI ON N1_ID = jft_Set.SRC AND N2_ID = jft_Set.DEST AND Group = jft_Set.Group
...