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
...
