[SOLVED] How can I select multiple tests name for same sample in one row instead of multiple rows?

How can I select multiple tests name for same sample in one row instead of multiple rows?

How can I select all test names in one row instead of multiple row in oracle SQL if these tests belongs to one sample_id ?

This is the query

select distinct d.sample_id, c.test_name_eng 
from mr_files a,
     lab_sample_header b,
     lab_tests c,
     lab_sample_details d,
     lab_orders o
where a.patient_no = b.patient_no 
  and b.order_id = d.order_id
  and c.test_no = d.test_no
  and a.patient_no = o.patient_no 
  and b.order_id = o.order_id
  and d.order_id = 2025000045
  and b.sample_id = d.sample_id
  and d.sample_status = 2;

Now the output :

SAMPLE_ID                  TEST_NAME   
BCH-25-100                 CALCIUM 
BCH-25-100                 SODIUM 
BCH-25-100                 ALBUMIN 
HE-25-43                   CBC

But I need the output if the tests have same sample ID appear in one row not multiple rows like this:

SAMPLE_ID                  TEST_NAME   
BCH-25-100                 CALCIUM ,SODIUM , ALBUMIN 
HE-25-43                   CBC

There is function in SQL server do this idea called `

string_agg

`But I don't know how to do it in oracle ?

Solution

You can use LISTAGG and GROUP BY sample_id to get comma separated values for test_names

select
    sample_id,
    LISTAGG(test_name_eng, ', ') WITHIN GROUP (ORDER BY test_name_eng) AS test_names
FROM
test -- your tables and joins
GROUP BY
    sample_id ;