Home » SQL & PL/SQL » SQL & PL/SQL » Union of 4 different columns in Single Row (SQL, PLSQL)
icon5.gif  Union of 4 different columns in Single Row [message #655212] Wed, 24 August 2016 23:06 Go to next message
anil_apps
Messages: 34
Registered: November 2005
Location: India
Member

In one table (xx_priv_rol) with five columns of Char(1) with Value Y/N

privilage
EXP
IMP
RUN
VIW
ADN
USER_NAME

-- Below scripts are used
create table xx_priv_rol(privilage varchar2(100), EXP char(1),IMP char(1),RUN char(1), VIW char(1) ,ADN char(1), USER_NAME varchar2(15));
insert into xx_priv_rol values('role1','Y','N','Y','N','N','AAROHI');
insert into xxx_priv_rol values('role2','Y','Y','N','Y','Y','AAROHI');
insert into xx_priv_rol values('role3','Y','N','N','Y','N','AAROHI');

I want to get highest privilege for all role's in one row for one user
There can be n number of records with different role with same user

I am expecting below result

EXP|IMP|RUN|VIEW|ADN
--------------------
Y | Y | Y | Y | Y


how can I achieve this?

Please suggest
Re: Union of 4 different columns in Single Row [message #655213 is a reply to message #655212] Thu, 25 August 2016 00:11 Go to previous messageGo to next message
Littlefoot
Messages: 21674
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Select MAX of each of them, GROUP BY user name.
Re: Union of 4 different columns in Single Row [message #655216 is a reply to message #655212] Thu, 25 August 2016 00:25 Go to previous messageGo to next message
Michel Cadot
Messages: 67879
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

Also always post your Oracle version, with 4 decimals, as solution depends on it.

Re: Union of 4 different columns in Single Row [message #655217 is a reply to message #655216] Thu, 25 August 2016 00:35 Go to previous messageGo to next message
anil_apps
Messages: 34
Registered: November 2005
Location: India
Member

Thank Very much Littlefoot, its works

[Updated on: Thu, 25 August 2016 00:36]

Report message to a moderator

Re: Union of 4 different columns in Single Row [message #655218 is a reply to message #655217] Thu, 25 August 2016 00:44 Go to previous message
Michel Cadot
Messages: 67879
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please post the solution you found.

Previous Topic: Sequence partition clause
Next Topic: Query to get Middle Record from a table
Goto Forum:
  


Current Time: Sat Jun 12 22:06:05 CDT 2021