CODEWITHSUNDEEP
sqloracle-databaseplsqlcategorization
Francisco Beisterveld
Francisco Beisterveld

Reputation: 1

How to categorize several columns in one statement in SQL/PLSQL

I've got a table with 20 columns which I like to categorize like;

0-25 --> 1
25-50 --> 2
50-75 --> 3
75-100 --> 4

I prefer not to use 20 case ... when statements. Anyone who knows how to do this more dynamically & efficiently? Can be SQL or PL/SQL.

I tried some PL/SQL, but I didn't see a simple method to use the column names as variables.

Many thanks.

Frans

Upvotes: 0

Views: 922

Answers (2)

user330315
user330315

Reputation:

Your example is a bit confusing, but assuming you want to put a certain value into those categories, the function width_bucket might be what you are after:

Something like this:

with sample_data as (
   select trunc(dbms_random.value(1,100)) as val
   from dual
   connect by level < 10
)
select val, width_bucket(val, 0, 100, 4) as category
from sample_data;

This will assign the numbers 1-4 to the (random) values from sample_data. the 0, 100 defines the range from which to build the buckets, and the final parameter 4 says in how many (equally wide) buckets this should be distributed. The result of the function is the bucket into which the value val would fall.

SQLFiddle example: http://sqlfiddle.com/#!4/d41d8/10721

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270653

The case statement is probably the most efficient way of doing it. A more dynamic way would be to create a table using the with statement. Here is an example of the code:

with ref as (
      select 0 as lower, 25 as higher 1 as val from dual union all
      select 25, 59, 2 from dual union all
      select 50, 75, 3 from dual union all
      select 75, 100, 4 from dual
     )
select ref.val
from t left outer join ref
     on t.col >= ref.lower and t.col < ref.higher

That said, this particular lookup could be done with arithmetic:

select trunc((t.col - 1) / 25) + 1 as val
from t

And, if your problem is managing the different columns, you might consider unpivot. However, I think it is probably easier just to write the code and modify the column names in a text editor or Excel.

Upvotes: 1

Related Questions