Re: Sql to concatenate multiple rows to a single row / column

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;

.

Relevant Pages

  • Re: How to delete rows?
    ... > foreach will iterate over, ... the RowState will get set to Deleted. ... > | attempt to read a deleted row will throw an exception. ... > | because it seems like calling Delete from for i= and from foreach ...
    (microsoft.public.dotnet.general)
  • Re: Checking to see if a user is a domain Administrator
    ... It would appear calling the advapi32.dll",EntryPoint = "LogonUser" is not reliable, or particularly fast. ... > foreach(object member in (IEnumerable) members) ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Checking to see if a user is a domain Administrator
    ... It would appear calling the advapi32.dll",EntryPoint = "LogonUser" is not reliable, or particularly fast. ... > foreach(object member in (IEnumerable) members) ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Checking to see if a user is a domain Administrator
    ... It would appear calling the advapi32.dll",EntryPoint = "LogonUser" is not reliable, or particularly fast. ... > foreach(object member in (IEnumerable) members) ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: How to delete rows?
    ... The DataTable.Select method will return an array of DataRows, ... foreach will iterate over, ... | attempt to read a deleted row will throw an exception. ... | because it seems like calling Delete from for i= and from foreach results ...
    (microsoft.public.dotnet.general)