Re: Sql to concatenate multiple rows to a single row / column

> Is there a way of concatenating the results from one column / multiple > rows into a one column / one row? > > Problem: > > I have a patient table with the following columns > > patid > patname > > allergy table with the following columns > > patid > allergy > > patid patname > ============== > 1 pat1 > 2 pat2 > 3 pat3 > > patid allergy > ============= > 1 allergy1 > 1 allergy2 > . > . > 1 allergyn > 2 allergy1 > 2 allergy2 > . > . > . > how can I write a query to generate the following result > > patid patname allergy > ================== > 1 pat1 allergy1,allergy2,....,allergyn > 2 pat2 allergy1,allergy2,....,allergyn > > Any help woud be greatly appreciated. > Thanks ! If your IDS version is more than 7 You can do this with MULTISET Select patid, patname,multiset (select item allergy from allergy where allergy.patid = patient.patid) from patient And you can get it as string Select patid, patname, replace(replace(replace(multiset (select item allergy from allergy where allergy.patid = patient.patid)::lvarchar , 'MULTISET{'''), '''}'),'''') from patient

Thank you SaltTan - it works beautifully. BTW, we have IBM Informix Dynamic Server Version 9.40.FC6.