RE: Sql to concatenate multiple rows to a single row / column
Ok, I'm too curious. Does multiset work in IDS7.2?
Rob
-----Original Message-----
From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org]
On Behalf Of bozon
Sent: Wednesday, November 16, 2005 10:55 AM
To: informix-list@iiug.org
Subject: Re: Sql to concatenate multiple rows to a single row / column
Very cool. I was doing it the old fangled way. Nice to know that I can
always learn something.
8.5 is the Extended Parallel Server which is great for data warehouses
but hasn't really caught on.
One quick note since you know that the string "MULTISET{" occurs at the
begining of the lvarchar there is no sense in searching the whole
multiset for it you can just use the substr function to remove it. (It
could also be wrong because one of your patients could be allergic to
"MULTISET{" ;-) )
Select patid, patname,replace(replace(substr(multiset (select item allergy from allergy where
allergy.patid =
patient.patid)::lvarchar,11),"'",""),"}","")
from patient ;
sending to informix-list