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

nicely done, stinky.

;-)

stinky wrote:
>
> 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 - 17:00:01 CDT