Group values: CASE text, ELSE field value

Question

I have a ROAD table:

+----+------------+
| ID | ROAD_CLASS |
+----+------------+
| 1 | ARTERIAL A |
| 2 | ARTERIAL B |
| 3 | ARTERIAL B |
| 4 | ARTERIAL C |
| 5 | ARTERIAL C |
| 6 | ARTERIAL C |
| 7 | COLLECTOR |
| 8 | COLLECTOR |
| 9 | LOCAL |
| 10 | LOCAL |
+----+------------+

The data type for the ROAD_CLASS field is NVARCHAR2.

I would like to create a view that groups all arterial roads in to a single ARTERIAL category, but leaves the other road classes as-is:

+------------+
| ROAD_CLASS |
+------------+
| ARTERIAL |
| COLLECTOR |
| LOCAL |
+------------+

How can I do this?

What I've tried:

I've successfully done part of it:

SELECT
 (CASE 
 WHEN ROAD_CLASS = 'ARTERIAL A' THEN 'ARTERIAL'
 WHEN ROAD_CLASS = 'ARTERIAL B' THEN 'ARTERIAL'
 WHEN ROAD_CLASS = 'ARTERIAL C' THEN 'ARTERIAL'
 --ELSE ROAD_CLASS
 END) AS ROAD_CLASS_GROUPED
FROM
 USER.ROAD
GROUP BY
 CASE 
 WHEN ROAD_CLASS = 'ARTERIAL A' THEN 'ARTERIAL'
 WHEN ROAD_CLASS = 'ARTERIAL B' THEN 'ARTERIAL'
 WHEN ROAD_CLASS = 'ARTERIAL C' THEN 'ARTERIAL'
 END
+--------------------+
| ROAD_CLASS_GROUPED |
+--------------------+
| null |
| ARTERIAL |
+--------------------+

However, instead of the other road classes being output as null, I'd like to get their field values (COLLECTOR and LOCAL).

When I try adding ELSE ROAD_CLASS to the CASE statement (as demonstrated in the SQL comment) I get an ORA-12704: character set mismatch error.

Answer 1

How about ...

select distinct 
 (case
 when road_class in('ARTERIAL A','ARTERIAL B','ARTERIAL C') then n'ARTERIAL'
 else road_class
 end ) as ROAD_CLASS_GROUPED
from user.road;

Due to the fact that the datatype of the column road_class is NVARCHAR, we need to write a N or n in front of the string literal 'ARTERIAL' in order to prevent the error message. (see also: https://docs.oracle.com/database/121/SQLRF/sql_elements003.htm#SQLRF00218 )

• stefan: @Wilson: done. I am new here. Where are all the other comments gone?
• User1974: Thanks! I put in a flag: Can the comments be cleaned up by a moderator? The problem has been solved, via a discussion in the comments. And the result has been put in the question/answer. I don't believe the comments are of value anymore.
• stefan: Phew! I thought I'd deleted them inadvertently (and thereby cheesing off a few fellow users). Thanks for letting me know!