How to concat the values of case statement values in a single column in Oracle database-CodePudding

select
 case
   when s.X344731='Y' then 'Property Tax Assessment'
   when s.X344732='Y' then 'Property Tax Payment'
   when s.X344733='Y' then 'Birth and Death Certificate'
   when s.X344734='Y' then ' Trade License'
   when s.X344735='Y' then ' Complaints/Grievance Redressal'
   when s.X344736='Y' then 'Water Connection'
   when s.X344737='Y' then 'Sewerage Connection'
   when s.X344738='Y' then 'Online Building Plan Approval System'    
   when s.X344739='Y' then ' Fire NOC'    
   when s.X3447310='Y' then ' NOC (Others)'    
   when s.X3447311='Y' then 'Certificates (Others)'    
   when s.X3447312='Y' then 'User Charges (water charges, parking fee, advertisement charges, electricity charges etc. )'    
   when s.X3447313='Y' then 'All of the above'    
   when s.X3447314='Y' then 'None of the above'    
   else 'Not Answered' end as municipal_services_do_you_use    
from lime_survey_988773 s 
left join lime_tokens_988773 t 
on (s.token=t.token)     
where s.submitdate is not null 
  and user_id=143561;

I want to concat the values of these three 'Property Tax Assessment', 'Property Tax Payment' and 'Birth and Death Certificate' it will look like this:-

User_id  Municipal_Services_Do_you_use                                                      State
142351   ('Property Tax Assessment', 'Property Tax Payment','Birth and Death Certificate')  Delhi

CodePudding user response：

You can use conditional aggregation with concatenation operators(||) such as

SELECT user_id AS "User_id",
       MAX(CASE WHEN s.X344731='Y' THEN '(''Property Tax Assessment''' END)||
       MAX(CASE WHEN s.X344732='Y' THEN ',''Property Tax Payment''' END)||
       MAX(CASE WHEN s.X344732='Y' THEN ',''Birth and Death Certificate'')' END) 
       AS "Municipal_Services_Do_you_use"     
  FROM lime_survey_988773 s 
  LEFT JOIN lime_tokens_988773 t 
    ON t.token = s.token  
 WHERE user_id = 143561 
 GROUP BY user_id

would return the desired result provided that at least one 'Y' value returns for each column with the name like X344... while user_id = 143561 after joining those tables.