BreenDeen BreenDeen -3 years ago 50
SQL Question

Oracle SQL Retrieving Columns based on conditions all in the same table

I have an Oracle view which needs to be used to populate different names on a page. The names of the page are filled based on the search criteria using the same database columns. The value assigned to the name simply depends on the search criteria. To make this more concrete, here is an example

EMPLOYEE_ID DEPT ROLE_TYPE GROUP_TYPE
123 IT DEVELOPER SUPPORT
111 IT DEVELOPER APPL_SERVICES
145 IT QA APPL_SERVICES
222 IT WEB APPL_SERVICES
322 IT WEB SUPPORT


The field names/categories to be populated by this table are as follows

WEB SUPPORT SERVICES
WEB APPLICATION SERVICES
DEVELOPER APPLICATION SERVICES
DEVELOPER SUPPORT SERVICES
QA APPLICATION SERVICES
QA SUPPORT SERVICES


The logic is as follows for using SQL Filters:

where ROLE_TYPE='DEVELOPER' AND GROUP_TYPE='APPL_SERVICES'
Then assign employee 111 to DEVELOPER APPLICATION SERVICES

where ROLE_TYPE='DEVELOPER' AND GROUP_TYPE='SUPPORT'
Then assign employee 123 to DEVELOPER SUPPORT SERVICES

where ROLE_TYPE='WEB' AND GROUP_TYPE='SUPPORT'
Then assign employee 322 to WEB SUPPORT SERVICES

where ROLE_TYPE='WEB' AND GROUP_TYPE='APPL_SERVICES'
Then assign employee 222 to WEB APPLICATION SERVICES

and so on..


My question is what's the best way to structure the query without having to make multiple queries? I don't want to write an individual query for each condition.

To clarify what I mean by here's what the UI display would look like:

USER FUNCTION POSITION GROUP
123 DEVELOPER SUPPORT SERVICES DEVELOPER SUPPORT SERVICES
111 DEVELOPER APPLICATION SERVICES DEVELOPER APPLICATION SERVICES
145 QA APPLICATION SERVICES QA SUPPORT SERVICES
222 WEB APPLICATION SERVICES WEB DEVELOPER APPLICATION SERVICES

Thank you

Answer Source

You just use case:

select t.*, 
       (case when ROLE_TYPE = 'DEVELOPER' AND GROUP_TYPE = 'APPL_SERVICES'
             then 'DEVELOPER APPLICATION SERVICES'
             when  ROLE_TYPE ='DEVELOPER' AND GROUP_TYPE = 'SUPPORT'
             then 'DEVELOPER SUPPORT SERVICES'
             when ROLE_TYPE = 'WEB' AND GROUP_TYPE = 'SUPPORT'
             then 'WEB SUPPORT SERVICES'
             when ROLE_TYPE = 'WEB' AND GROUP_TYPE = 'APPL_SERVICES'
             then 'WEB APPLICATION SERVICES'
        end) as new_group
from . . .
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download