[SQL] Overlapping Ranges- Query Alternative

[Ozer, Pam]

I have the following problem:

 

Create temp table ranges (Groups int, ColumnA int);

Insert into ranges

Values(2,45);

 

Select Groups, 

Case when ColumnA between 0 and 19 then 0

     when ColumnA >=20 then 20

     when ColumnA >=30 then 30

     when ColumnA>=40 then 40

     when ColumnA>=50 then 50 end MinRange

from ranges

 

Results: 

Groups minrange

2;20

 

What I want Is : One column can fall into multiple ranges.  For example
45 >20, 30, and 40 so I want the following results

2;20

2;30

2;40

 

I know I could do a union with each range but is there any way to bring
back all ranges in one query?  I need to bring back the values in one
column so having separate columns for each range is not an option.

 

Thank you in advance for any help

 

Pam Ozer