Hi All,
I am looking for a solution to display values in multiple rows into single row with comma separated:
I have a query that outputs the data in multiple rows. The query as below:
select
accTab.lanid Access\_ID,
accTab.username Access\_Name,
nar.NAME Access\_Profile,
accTab.login\_type Access\_Type,
accTab.country Access\_Country,
accTab.source\_system Access\_SourceIP,
accTab.source\_host Access\_SourceHost,
(to\_char(accTab.login\_date,'DDMMYYYY HH12.MI.SS AM')) Access\_LoginTimeStamp,
(to\_char(accTab.logout\_date,'DDMMYYYY HH12.MI.SS AM')) Access\_LogoutTimeStamp
from
(
select
lanid,
username,
login\_type,
country,
source\_system,
source\_host,
login\_date,
logout\_date,
rank() over (
partition by lanid
order by login\_date desc
) rnk
from ACCESSLOG
) accTab
inner join n_authorization naa on accTab.LANID = naa.windows_identity
inner join n_roles nar on naa.role_id=nar.role_id
where
accTab.login_date > sys_extract_utc(systimestamp - interval '1' hour)
order by accTab.login_date;
The above query displays the output as below:
For single Access_ID, there will multiple Access_Profiles(no specific number count). So I want to display the above output in single row with comma separated as displayed below:
I came across threads about the usage of 'CEIL', but I couldn't find the way. Any solutions would be very helpful.
Oracle 11G
SQL Developer
Note: For one Access_ID, only Access_Profile will have multiple values