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