Sql to concatenate multiple rows to a single row / column

[Reddy]

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 !

[scottishpoet]

This should do the sort of thing you want, the only caviat is the
function will run for every row returned by the calling select.

If I wrote it ia proper programming language I'd probably do it
differently.

CREATE FUNCTION cat_parts(p_num INTEGER) RETURNING CHAR(255);

DEFINE tmp_str, ret_str, ret_val CHAR(255);

LET ret_Str = "Allergies";
LET ret_val = "";

FOREACH SELECT allergy INTO ret_val FROM allergy
WHERE patid = p_num
AND allergy IS NOT NULL
LET tmp_str = ret_str;
LET ret_str = TRIM(tmp_str) ||" ; " || TRIM(ret_val);
END FOREACH

RETURN ret_str;

END FUNCTION;

SELECT patid, patnum , cat_parts(patid) FROM patient;

[Reddy]

Thank You Scottishpoet !

[Reddy]

Thank You Scottishpoet !

[bozon]

If you know the maximum number of allergies that your patients have you
can do a N Way join. If you are happy with a report that shows 6 common
allergies then the following would work:

create temp table patient(
patid serial,
patname varchar(25)
) ;

create temp table allergy(
allerid serial,
allergy varchar(25),
patid int
) ;

insert into patient values (0,"pat1");
insert into patient values (0,"pat2");
insert into patient values (0,"pat3");

insert into allergy values (0,"allergy1",1) ;
insert into allergy values (0,"allergy2",1) ;
insert into allergy values (0,"allergy3",1) ;
insert into allergy values (0,"allergy4",1) ;
insert into allergy values (0,"allergy5",1) ;
insert into allergy values (0,"allergy6",1) ;

insert into allergy values (0,"allergy1",2) ;
insert into allergy values (0,"allergy2",2) ;
insert into allergy values (0,"allergy3",2) ;
insert into allergy values (0,"allergy4",2) ;

insert into allergy values (0,"allergy1",3) ;
insert into allergy values (0,"allergy2",3) ;
insert into allergy values (0,"allergy3",3) ;

select * from patient ;
select * from allergy ;

select distinct
p.patid,
patname,
max(
case a1.allergy
when "allergy1" then "X"
else null
end
),
max(
case a2.allergy
when "allergy2" then "X"
else " "
end
),
max(
case a3.allergy
when "allergy3" then "X"
else null
end
),
max(
case a4.allergy
when "allergy4" then "X"
else null
end
),
max(
case a5.allergy
when "allergy5" then "X"
else null
end
),
max(
case a6.allergy
when "allergy6" then "X"
else null
end
)
from
patient p,
allergy a1,
outer(
allergy a2,
allergy a3,
allergy a4,
allergy a5,
allergy a6
)
where
p.patid = a1.patid and
p.patid = a2.patid and
p.patid = a3.patid and
p.patid = a4.patid and
p.patid = a5.patid and
p.patid = a6.patid and
a1.allerid <> a2.allerid and
a2.allerid <> a3.allerid and
a3.allerid <> a4.allerid and
a4.allerid <> a5.allerid and
a5.allerid <> a6.allerid
group by
1,2
;

OUTPUT:

patid patname

1 pat1
2 pat2
3 pat3

allerid allergy patid

1 allergy1 1
2 allergy2 1
3 allergy3 1
4 allergy4 1
5 allergy5 1
6 allergy6 1
7 allergy1 2
8 allergy2 2
9 allergy3 2
10 allergy4 2
11 allergy1 3
12 allergy2 3
13 allergy3 3

patid patname a1 a2 a3 a4 a5 a6

1 pat1 X X X X X X
2 pat2 X X X X
3 pat3 X X X

With a little more work you can get the count of patients that have
each of these allergies. If you need more help just reply to this
thread and I'll monitor it.

[SaltTan]

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

[Obnoxio The Clown]

SaltTan said:
>
> If your IDS version is more than 7
> You can do this with MULTISET

Not entirely true. I tried this repeatedly with 8.50 and got absolutely
nowhere. :o)

--
Bye now,
Obnoxio

"C'est pas parce qu'on n'a rien ` dire qu'il faut fermer sa gueule"
- Coluche

did i mention i like nulls? heck, i even go so far as to say that all
columns in a table except the primary key could/should be nullable. this
has certain advantages, for example, if you need to insert a child record
and you don't have a parent row for it, just do an insert into the parent
table with the primary key value (everything else null), and voila,
relational integrity is preserved. but this is, admittedly, a bit
controversial among modellers.

--r937, dbforums.com
sending to informix-list

[SaltTan]

> SaltTan said:
> >
> > If your IDS version is more than 7
> > You can do this with MULTISET
>
> Not entirely true. I tried this repeatedly with 8.50 and got absolutely
> nowhere. :o)

Is it IDS?

[Reddy]

Thank You for your help

[Reddy]

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

[bozon]

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 ;

[Konikoff, Rob (Contractor)]

Ok, I'm too curious. Does multiset work in IDS7.2?

Rob

sending to informix-list

[Obnoxio The Clown]

Konikoff, Rob \(Contractor\) said:
>
> Ok, I'm too curious. Does multiset work in IDS7.2?

No.

[Jean Sagi]

What this exactly do ?

Select patid, patname,
replace(replace(substr(multiset (select item allergy from allergy where
allergy.patid =
patient.patid)::lvarchar,11),"'",""),"}","")
from patient ;

substr from a multiset ?

J.

bozon escribis:

[SaltTan]

Jean Sagi писал(а):

> What this exactly do ?
>
> Select patid, patname,
> replace(replace(substr(multiset (select item allergy from allergy where
> allergy.patid =
> patient.patid)::lvarchar,11),"'",""),"}","")
> from patient ;
>
> substr from a multiset ?
>

substr from lvarchar

[Obnoxio The Clown]

No.

--
Bye now,
Obnoxio

sending to informix-list