CODEWITHSUNDEEP
sqloracle-database
user1528135
user1528135

Reputation: 1

How to take rows from Oracle separated by comma

This is what I'm getting

parent   MenuName Name     menu2    type      menuId   menu2Id
--------------------------------------------------------------
26  General Currency    Add 3   27  29
26  General Currency    Delete  3   27  31
26  General Currency    Update  3   27  30
26  General Currency    View    3   27  28
26  General Country Add 3   32  34
26  General Country Delete  3   32  36
26  General Country Update  3   32  35

I want to get like this

MenuId   MenuName   Name       Privilege
------------------------------------------------
27       General    Currency   Add,Delete,Update,View
32       General    Country    Add,Delete,Update

Please help with this

Thx in advance.

Upvotes: 0

Views: 1415

Answers (3)

A.B.Cade
A.B.Cade

Reputation: 16915

If you're not using oracle 11gR2 then there are other ways - read here

And the XMLAGG way:

select menuid, menuname, name, trim(xmlagg(xmlelement(e, privilege || ','))
              .extract('//text()')) Privilege
from table_name
group by MenuId,MenuName,Name

Upvotes: 1

pratik garg
pratik garg

Reputation: 3342

you can use listagg function for the same like follow -

select MenuId,
       MenuName,
       Name, 
       listagg(menu2,',') within group (order by 1) Privilege
from table_name
group by MenuId,MenuName,Name

for reference

Upvotes: 1

user330315
user330315

Reputation:

You did not state your version, so I assume the current version (11.2):

select menuid, menuname, name, listagg(privilege, ',')
from menu
group by menuid, menuname, name

Upvotes: 1

Related Questions