Re: Help with SQL to concatenate row data ...

From: stinky <stankonia_at_stunky.org>
Date: Sun, 21 Apr 2002 16:04:31 -0400
Message-ID: <3CC31B4F.7090801@stunky.org>

Anti Spam wrote:

>I am hoping that someone can help me with a tip for a SQL problem that I
>have.
>
>Given an example table "MYCOLORS" which has the following 5 row entries:
>
>ID COLOR
>-- -----
>1 VIOLET
>1 INDIGO
>2 BLUE
>2 GREEN
>2 YELLOW
>3 ORANGE
>
>I would like to write SQL that produces the following output
>
>ID MIXED
>-- ------
>1 VIOLET,INDIGO
>2 BLUE,GREEN,YELLOW
>3 ORANGE
>
>In other words, I am looking for a way to write a general SQL statement that
>aggregates the COLOR rows into single fields, grouped by the ID. Be advised
>that, beyond this example, I may not know how many different colors exist in
>the MYCOLORS table at any time.
>
>Can anyone help?
>
>Thanks,
>Charles
>
>

Since you don't know how many colors you'll have per ID, SQL is inappropiate. You need to handle this type of problem with a procedural language. Either PL/SQL or Java. Here's some PL/SQL code to give you an idea on how to attack the problem:

 1 DECLARE
  2 TYPE MyColorsTyp IS REF CURSOR;
  3 myc_cv MyColorsTyp;
  4 myc_rec mycolors%ROWTYPE;
  5 id_no_v Number;
  6 color_v Varchar2(50);
  7 BEGIN
  8 OPEN myc_cv FOR
  9 select id, color
 10 from mycolors
 11 order by id;
 12 LOOP

 13      FETCH myc_cv INTO myc_rec;
 14      IF myc_cv%NOTFOUND THEN
 15        dbms_output.put_line(id_no_v ||chr(9)|| color_v);
 16        EXIT;
 17      END IF;
 18      IF myc_cv%ROWCOUNT = 1 THEN
 19        dbms_output.put_line('ID'||chr(9)||'MIXED');
 20        dbms_output.put_line('--'||chr(9)||'-----');
 21        id_no_v := myc_rec.id;
 22        color_v := myc_rec.color;
 23      END IF;
 24      IF myc_cv%ROWCOUNT > 1 THEN
 25        IF id_no_v = myc_rec.id THEN
 26          color_v := color_v || ', ' || myc_rec.color;
 27        ELSE
 28          dbms_output.put_line(id_no_v ||chr(9)|| color_v);
 29          id_no_v := myc_rec.id;
 30          color_v := myc_rec.color;
 31        END IF;
 32      END IF;

 33 END LOOP;
 34 CLOSE myc_cv;
 35* END;
SQL> /

ID      MIXED
--      -----
1       VIOLET, INDIGO
2       BLUE, GREEN, YELLOW
3       ORANGE

PL/SQL procedure successfully completed. Received on Sun Apr 21 2002 - 15:04:31 CDT