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 !
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;
Thank You Scottishpoet !
Thank You Scottishpoet !
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.