Re: SQL Query: Concatenation of multiple records

Nick Heppleston wrote:

> Trying to solve a query which is completely beyond me. Any help would
> be greatly appreciated.
>
> We have 2 tables, the first a product table with each record relating
> to an individual product. The second table is a 'Long Description'
> table with multiple entries per product code (relating to a unique ID
> in the first table). So, for product '00ASD23' the long description is
> broken down into say five different records each with the product code
> '00ASD23'.
>
> The query needs to pull back individual records for each product (as
> in this first table), however each record must also contain the long
> description, made up from multiple records in the second table. These
> records must be in essence concatenated with each other and returned
> as one string that can be included into each individual product
> record.
>
> Again, any help would be greatly appreciated. If you require more
> information, please don't hesitate to contact me or reply to this
> posting.
>
> Cheers, Nick
> nick.heppleston_at_midwich.com

Hi Nick
Imagine your first table is ARTICLES (id, title ... ) and your second table is TEXTS of columns:
  article (article id),
  text (part of large text),
  sort_column (something that knows the order of text parts).

Having some column2row() function like that one given below you could use SQL statements like this:

SELECT title, column2row(id) FROM articles;

--

CREATE FUNCTION column2row( article_id IN NUMBER )
  RETURN VARCHAR2
AS
  ret_value VARCHAR2( 32767 ); -- this is the upper limit for VC2 in
PL/SQL
  CURSOR c_texts IS
    SELECT text FROM texts
      WHERE article = article_id
        ORDER BY sort_column;
BEGIN
  FOR text_value IN c_texts LOOP
    IF ret_value IS NULL THEN
      ret_value := text_value.text;
    ELSE
      ret_value := ret_value || ' ' || text_value.text;
    END IF;
  END LOOP;

  RETURN NVL(ret_value,'N/A');

END column2row;

Regards