I have a column with random data that ranges from (0 to n),
I want to select it so that I get a new column that goes up in increments of 5.
so 0 - 5, 5 - 10, so on and so forth.

Current Table:-

Expected Table:-

Everything I've tried doesn't really give a dynamic answer.

CodePudding user response：

You can just use arithmetics. Assuming a table like so:

create table t(id int, name varchar(100));
insert into t values (5, 'Hello2'), (8, 'Hello3'), (10, 'Hello');

We can compute the lower bound of the interval with this expression:

select t.*, 1   (id - 1) / 5 * 5 lbound from mytable

The 1/-1 gym is there because we want intervals that start at 1 rather than 0. With this information at hand, we can easily compute the upper bound (just 4 to the lower bound), and aggregate.

If you are running SQL Server (you tagged the question tsql):

select x.lbound, x.lbound   4 ubound, 
    string_agg(t.name, ', ') within group(order by t.id) names
from t
cross apply ( values ( 1   (t.id - 1) / 5 * 5) ) x(lbound)
group by x.lbound