How to change GROUPING value from 0/1 to no/yes?

user_12545295 0
Viewed 125

How i can change column Department ID Used and Job ID used value from 0/1 to no/yes?

enter image description here

this my query:

SELECT d.department_name "department name", e.job_id "job title",
       SUM(e.salary) "monthly cost",
       GROUPING(d.department_id) "Department ID Used" ,
       GROUPING(e.job_id) "Job ID Used"
  FROM employees e
  JOIN departments d
    ON e.department_id=d.department_id
 GROUP BY CUBE(d.department_name, d.department_id, e.job_id)
ORDER BY d.department_name, e.job_id
grouping oracle sql
2 Answers

You could add a CASE WHEN clause to your GROUPING BY :

CASE WHEN GROUPING(d.department_id) = 0 THEN 'NO' ELSE 'YES' END

CASE can be used in both SQL and PLSQL
whereas DECODE is specific to Oracle and can be used only in SQL.

user_95735180

Just replace

DECODE(GROUPING(department_id),0,'no','yes') with GROUPING(d.department_id)

and

DECODE(GROUPING(e.job_id),0,'no','yes') with GROUPING(e.job_id)

user_58413060
Related