asked on

A simple Group by in ORACLE needed

Hi Experts,

Here is a table: product
Product_id Tags
1 Football
1 Soccer
1 Tennisl
2 Football
2 Golf
3 Rugby

Now I want to group all the tags against a product_id and tried:
1)select product_id, tags from product group by product_id --> didnt work, oracle wants all the columns in select to be in group by
2)select product_id, tags from product group by product_id -> gave me these results:
Product_id Tags
1 Football
1 Soccer
1 Tennisl
2 Football
2 Golf
3 Rugby

Which is basically the same table data. I think the above query woprks in SQL server 200 but we have Oracle here.

What i want is this:
Product_id Tags
1 Football, Soccer, Tennis
2 Football, Golf
3 Rugby

anandmahajan

I think this is what you are looking for:

------------------Create a function first-----------------------------------------
CREATE OR REPLACE function myfunc1(in_id in number)
return varchar2
IS
v_ret varchar2(100) := ' ';
begin
for rec in (select tags from product where pid = in_id)
loop

IF (length(v_ret) > 1 ) then
v_ret := v_ret||','||rec.tags;
ELSE
v_ret := v_ret||rec.tags;
END IF;
end loop;
return v_ret;
end;

----------------------------------------------------------------------
then just run following statement:

select distinct pid,myfunc1(pid) tags from product;

hope this will solve your problem.

Madhu_A

ASKER

Please read my second sql as:
2)select product_id, tags from product group by product_id, tags -> gave me these results:
Product_id Tags
1 Football
1 Soccer
1 Tennisl
2 Football
2 Golf
3 Rugby
--------

Anand, thanks for your response but I am not the owner of the database and will not be finding myself creating a function in the DB. Is there a way to do it, with just sql queries?

ASKER CERTIFIED SOLUTION

anandmahajan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION