asked on

need help on sql query

Hi ,

I have an table called customer with the below structure.

customer_id
is_internal
is_external
is_vip

The fields is_internal, is_external,is_vip store values like Y or N.

My requirement is I need to check these fields for each customer Id , and sort them by concantenating values present in is_internal, is_external,is_vip.

for example
customer id Is_internal is_external is_vip customer type
1 Y Y Y internal,external,vip
2 Y Y N internal,external
3 Y N Y internal, vip
4 N Y Y external, vip
5 N Y N external
6 N N Y vip

i must write an query which fetches the results as in customer_id , customer_type
and sort based on customer_type.

Select customer_id ,
case when is_internal ='Y' and is_external='Y' and is_vip='Y' then
'INTERNAL'||','||'EXTERNAL'||','||'VIP'
when is_internal ='Y' and is_external='Y' and is_vip='N' then
'INTERNAL'||','||'EXTERNAL'
when is_internal ='Y' and is_external='N' and is_vip='N' then
'INTERNAL'
WHEN is_internal ='Y' and is_external='N' and is_vip='Y' then
'INTERNAL'||','||'VIP'
When is_internal ='N' and is_external='N' and is_vip='Y' then
'VIP'
when is_internal ='N' and is_external='Y' and is_vip='Y' then
'EXTERNAL'||'VIP'
when is_internal ='N' and is_external='Y' and is_vip='N' then
'EXTERNAL'
end customertype
from customer
order by 2 asc

Is there an better approach to this requirement. can I use list_agg function to achive the result.

Any help is really appreciated.

ASKER CERTIFIED SOLUTION

MikeOM_DBA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Swaminathan K

ASKER

when only is_internal is true , Iam getting INTERNAL, . I do not want the , to be appended , if only one condition is satisfied

For example :
is_internal="Y' and is_external='N' and is_vip='N'

it should display only INTERNAL and not INTERNAL,

MikeOM_DBA

And is it too difficult for you to remove the trailing comma?

HINT: Use the RTRIM() function.

Swaminathan K

ASKER

thanks a lot.