Concat columns

[smn198]

Hi All

I have the following table:

ID NAME
-----------------------
1 Bob
1 Chris
2 Harry
2 Joe
2 Tim
3 Tom
4 John
5 Larry


Is there any way at all to return this:


ID NAMES
-------------------------------------
1 Bob, Chris
2 Harry, Joe, Tim
3 Tom
4 John
5 Larry


Thanks for your help.

Steve

 

[JamesLean]

See my answer in thread220-1059252

--James

 

[smn198]

Sorry. I didn't specify. I need ANSI SQL.

Thanks

 

[swampBoogie]

create function hepsomsut(p_id int)
returns varchar(200)
reads sql data
begin
    declare a cursor for select name 
        from t 
       where id = p_id
         and name is not null;
    declare v_name varchar(20);
    declare v_names varchar(200);
    open a;
    begin 
        declare exit handler for not found begin end;
        loop
            fetch a into v_name;
            set v_names = coalesce(v_names || ', ','') 
                       || v_name;
        end loop;
   end;
   close a;
   return v_names;
end

select distinct id,hepsomsut(id)
  from t

change names and types as apropriate.

 

[smn198]

Thanks swampBoogie

Unfortunately I cannot use functions

 

[JamesLean]

Again, as per my first post, you should post this question in a forum specific to your DBMS. swampBoogie has provided an ANSI compliant solution, which you asked for, but you have now said that you can't use functions!

Posting in the correct forum will get you the best answer in the quickest time. Depending on which DBMS you are using, there may be a proprietary solution which you will not get in a fourm dedicated to ANSI standard SQL.

--James

 

[smn198]

Never mind then