Thread: SQL syntax for concating values in different rows together
SQL syntax for concating values in different rows together
From
"Elizabeth O'Neill's Office Mail"
Date:
Hi Can someone please help me. I have two tables in my database a complaint table and a resolution table. One complaint may have several resolutions. I am trying to build a report that will give me the complaint details and all the resolution descriptions for a complaint in one text area/row (concated together). At the moment it is repeating the complaint details for each resolution. Any help would be gratefully received Cheers Liz
On Tue, Dec 10, 2002 at 12:27:34PM +0000, Elizabeth O'Neill's Office Mail wrote: > Hi > > Can someone please help me. > > I have two tables in my database a complaint table and a resolution table. > One complaint may have several resolutions. I am trying to build a report > that will give me the complaint details and all the resolution descriptions > for a complaint in one text area/row (concated together). > > At the moment it is repeating the complaint details for each resolution. You could create a PL/pgSQL function that would take the id of the resolution to query the complaints table and return the concatenation of all the details. That way you could just: SELECT id, foo, bar, concat_resolution_details(id) FROM resolutions; I'd give it a shot at the function, but you didn't give the structure of the table. -Roberto -- +----| Roberto Mello - http://www.brasileiro.net/ |------+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ +
Elizabeth O'Neill's Office Mail wrote: > I have two tables in my database a complaint table and a resolution table. > One complaint may have several resolutions. I am trying to build a report > that will give me the complaint details and all the resolution descriptions > for a complaint in one text area/row (concated together). > > At the moment it is repeating the complaint details for each resolution. As someone else mentioned I think, you can use a plpgsql function. Here is a contrived example: create table complaint(cid int, descr text); insert into complaint values(1,'my #1 complaint'); insert into complaint values(2,'my #2 complaint'); create table resolution(rid int, cid int, res text); insert into resolution values (1,1,'fixed it'); insert into resolution values (2,1,'really fixed it!'); insert into resolution values (3,2,'pbkbac again'); create or replace function my_concat(int) returns text as' declare ret text; comp text; rec record; cntr int = 0; begin select into comp descr from complaint where cid = $1; ret := ''Comp = '' || comp; for rec in select res from resolutionwhere cid = $1 loop cntr := cntr + 1; ret := ret || '': Res# '' || cntr::text || '' = '' || rec.res; endloop; return ret; end; ' language 'plpgsql'; regression=# select my_concat(cid) from complaint; my_concat ---------------------------------------------------------------------- Comp = my #1 complaint: Res# 1 = fixed it: Res# 2= really fixed it! Comp = my #2 complaint: Res# 1 = pbkbac again (2 rows) In the past I think I remember someone trying to solve this kind of problem with a custom aggregate, but the plpgsql approach is probably simpler. HTH, Joe