i have a table 1 shown below
Name role F1 status1 status 2
sam player yes null null
sam admin yes null null
sam guest no x x
i want the result to be
Name role status1 status 2
sam admin,player x x
i have done a query to list_agg the role in to one row.but the status is null for sam to show when F1='yes'
query i used
select name,list_agg(role,',') within group(order by name),max(status1),max(status2)
from table 1 where F1='yes'
group by name
but i get something like this
name role status1 status2
sam admin,player null null
i want the where to work only on role column and the max(status1) to be in status1 i.e.'x'.please help me .thank you
You can try using LISTAGG()
within a GROUP BY
query:
SELECT Name,
LISTAGG(Role, ',') WITHIN GROUP (ORDER BY Role) "Role"
MAX(CASE WHEN water_access = 'Y' THEN 'Y' ELSE NULL END) "water_access",
MAX(CASE WHEN food_access = 'Y' THEN 'Y' ELSE NULL END) "food_access",
MAX(CASE WHEN power_access = 'Y' THEN 'Y' ELSE NULL END) "power_access"
FROM yourTable
GROUP BY Name
ORDER BY Name DESC
Note that I chose to order the aggregation of each Name
group using the Role
, because you didn't provide us with any column which could give the ordering you show in your expected output.
Second note: MAX()
in Oracle ignores NULL
values, so it can be used in the pivot to correctly identify the Y
values you want to appear.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments