TRicky SQL

Question

Post reply

TRicky SQL

kolangara_anand

Mr or Mrs. 500

Points: 552
More actions
January 11, 2010 at 10:20 pm

#100042

I have the below table with the values inserted as shown
create table agegrp(
ageid int identity (1,1) not null,
agetill int)
insert into agegrp values (0);
insert into agegrp values (5);
insert into agegrp values (18);
insert into agegrp values (50);
insert into agegrp values (75);
insert into agegrp values (100);
I want the output as below (agefrom-agetill)
0-5
5-18
18-50
50-75
75-100
i.e no overlapping of the ages. Can someone suggest the best way to get that?
Regards,
Anand.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

thank you so much for the sample data! makes it so easy to help!

you can do it by joining the table to itself, with a WHERE statement that is a.identity=b.identity + 1:

sample results:

agetill agetill

----------- ----------- -------------------------------------------------------------

NULL 0 NULL

0 5 0-5

5 18 5-18

18 50 18-50

50 75 50-75

75 100 75-100

the SQL:

select b.agetill ,a.agetill,convert(varchar,b.agetill) + '-' + convert(varchar,a.agetill)

from agegrp a

left outer join agegrp b

on a.ageid = b.ageid +1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Lowell's solution should work nicely for you.

One question - will the order of the records always be the same (agegroups will not get out of order)?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

kolangara_anand Mr or Mrs. 500 Points: 552 More actions · Answer 3

Nopes it would not be. However have modified the solution provided to use CTE. works well now irrespective of the order of data input.

delete from agegrp;

insert into agegrp values (0);

insert into agegrp values (75);

insert into agegrp values (100);

insert into agegrp values (5);

insert into agegrp values (18);

insert into agegrp values (50);

with agecompare as (

select ageid,agetill,

row_number() over( order by agetill) as rownum

from agegrp )

select b.agetill as MINAGE,a.agetill as MAXAGE,

convert(varchar,b.agetill) + '-' + convert(varchar,a.agetill) as DROPDOWN

from agecompare a

left outer join agecompare b

on a.rownum = b.rownum +1

Thnx guys.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Thank you. You posted a question and still worked on solving it. Thanks for posting back the solution you found.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Mitesh Oswal SSCrazy Points: 2798 More actions · Answer 5

DECLARE @agegrp TABLE

(

AGE INT

)

insert into @agegrp values (0);

insert into @agegrp values (75);

insert into @agegrp values (100);

insert into @agegrp values (5);

insert into @agegrp values (18);

insert into @agegrp values (50);

SELECT A.AGE+'-'+B.AGE AS AGE FROM

(

SELECT

ROW_NUMBER() OVER (ORDER BY AGE)+1 AS ROW,

CAST(AGE AS VARCHAR(10)) AS AGE

FROM

@agegrp

)A

INNER JOIN

(

SELECT

ROW_NUMBER() OVER (ORDER BY AGE) AS ROW,

CAST(AGE AS VARCHAR(10)) AS AGE

FROM

@agegrp

)B

ON

A.ROW=B.ROW

Regards,
Mitesh OSwal
+918698619998