[SQL] SQL syntax for concating values in different rows together

[Elizabeth O'Neill's Office Mail]

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



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] SQL syntax for concating values in different rows together

[Roberto Mello]

On Tue, Dec 10, 2002 at 12:27:34PM +, 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/ +

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [SQL] import sql script

[mark carew]

no



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] sql query

[Vijay Uppal]

Hi I have a problem forming a query

I have 3 tables salary(hrs, clock_in , clock_out) 

Break ( date, employe_id,
net_time, break_in, break_out)

Employee(employee_id, pay_rate,
name)

 

I need to get an hourly report till the current time for
that day stating name, hour, no of hours, salary

Example

 

Rob  3-4    48min   $6.56

Min   2-3    23min   $2.34

Min   3-4    54min   $7.67

 

And so on….Would appreciate help on this…since I
cant come up with the query

 

Vj 

Re: [SQL] SQL syntax for concating values in different rows together

[Joe Conway]

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 resolution where cid = $1 loop
cntr := cntr + 1;
ret := ret || '': Res# '' || cntr::text || '' = '' || rec.res;
  end loop;
  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


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

[SQL] is numeric relational operator problem fixed in v7.3

[jack]

Does the following now works in postgreSQL  v7.3?

Select * 
from a_table
where num1 >10;

*** type of num1 is NUMERIC (12,2)

Jack


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] is numeric relational operator problem fixed in v7.3

[Oliver Elphick]

On Wed, 2002-12-11 at 02:54, jack wrote:
> Does the following now works in postgreSQL  v7.3?
> 
> Select * 
> from a_table
> where num1 >10;
> 
> *** type of num1 is NUMERIC (12,2)

It works

-- 
Oliver Elphick <[EMAIL PROTECTED]>
LFIX Limited


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] Question

[Jonathan Man]

Hi,
 
Can I get a table structure on a function using 
pgsql??
 
 
Thank you very much!!
 
From JONATHAN MAN