Need Help - SQL Select Statement

Question

Need Help - SQL Select Statement

keshabsingh

SSC Veteran

Points: 232
More actions
August 27, 2014 at 9:49 am

#295766

Run this script
create table #table1(steps int)
insert into #table1(steps)values(1)
insert into #table1(steps)values(3)
insert into #table1(steps)values(4)
insert into #table1(steps)values(5)
select steps from #table1
drop table #table1
You will get this
steps
1
3
4
5
I want this way.
stepFrom stepTo
1 3
3 4
4 5
Hints: you need to join the same table multiple times.

Viewing 15 posts - 1 through 15 (of 26 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

keshabsingh (8/27/2014)
Run this script
create table #table1(steps int)
insert into #table1(steps)values(1)
insert into #table1(steps)values(3)
insert into #table1(steps)values(4)
insert into #table1(steps)values(5)
select steps from #table1
drop table #table1
You will get this
steps
1
3
4
5
I want this way.
stepFrom stepTo
1 3
3 4
4 5
Hints: you need to join the same table multiple times.

So what are you asking? Looking at this I know exactly what I need to do. Why not show us what you have tried so far.

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 2

keshabsingh (8/27/2014)
Run this script
create table #table1(steps int)
insert into #table1(steps)values(1)
insert into #table1(steps)values(3)
insert into #table1(steps)values(4)
insert into #table1(steps)values(5)
select steps from #table1
drop table #table1
You will get this
steps
1
3
4
5
I want this way.
stepFrom stepTo
1 3
3 4
4 5
Hints: you need to join the same table multiple times.

Looks like it's homework season again;-) Please tell the tutor that you only need one outer apply!

😎

create table #table1(steps int)

insert into #table1(steps)values(1)

insert into #table1(steps)values(3)

insert into #table1(steps)values(4)

insert into #table1(steps)values(5)

select

t1.steps

,t2.steps

from #table1 t1

outer apply #table1 t2

where (t1.steps + t2.steps) IN (4,7,9)

AND t1.steps < t2.steps

drop table #table1

Results

steps steps

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

1 3

3 4

4 5

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 3

where (t1.steps + t2.steps) IN (4,7,9) Only works for these values, in this example.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

rxm119528 SSCrazy Points: 2960 More actions · Answer 4

keshabsingh (8/27/2014)
Hints: you need to join the same table multiple times.

with CTE(rn,steps)

As

(

select row_number() over ( order by steps),steps from #table1)

select t1.steps,t2.steps from CTE t1

left outer join CTE t2

on t1.rn = t2.rn-1

where t2.steps is not null

keshabsingh SSC Veteran Points: 232 More actions · Answer 5

This is not homework session. Your approach is very limited with hard coding the values. What if data is like this?

create table #table1(steps int)

insert into #table1(steps)values(1)

insert into #table1(steps)values(2)

insert into #table1(steps)values(3)

insert into #table1(steps)values(4)

insert into #table1(steps)values(5)

insert into #table1(steps)values(6)

insert into #table1(steps)values(7)

insert into #table1(steps)values(9)

insert into #table1(steps)values(10)

insert into #table1(steps)values(17)

insert into #table1(steps)values(31)

select steps from #table1

I Want this way,

stepFrom stepTo

1 2

2 3

3 4

4 5

5 6

6 7

7 9

9 10

10 17

17 31

Sean Lange SSC Guru Points: 286573 More actions · Answer 6

keshabsingh (8/27/2014)
This is not homework session. Your approach is very limited with hard coding the values. What if data is like this?
create table #table1(steps int)
insert into #table1(steps)values(1)
insert into #table1(steps)values(2)
insert into #table1(steps)values(3)
insert into #table1(steps)values(4)
insert into #table1(steps)values(5)
insert into #table1(steps)values(6)
insert into #table1(steps)values(7)
insert into #table1(steps)values(9)
insert into #table1(steps)values(10)
insert into #table1(steps)values(17)
insert into #table1(steps)values(31)
select steps from #table1
I Want this way,
stepFrom stepTo
1 2
2 3
3 4
4 5
5 6
6 7
7 9
9 10
10 17
17 31

If it isn't homework then why does your initial post give a hint that you need to join multiple times? That is in fact one way this can be accomplished.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 7

WITH TEMP_CTE AS (

SELECT steps, ROW_NUMBER() OVER(ORDER BY steps) AS ROW_NUM FROM #table1

)

SELECT t1.steps, t2.steps FROM TEMP_CTE t1, TEMP_CTE t2

WHERE t1.ROW_NUM < (SELECT MAX(ROW_NUM) FROM TEMP_CTE)

AND (t2.ROW_NUM - 1) = t1.ROW_NUM

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 8

keshabsingh (8/27/2014)
This is not homework session. Your approach is very limited with hard coding the values. What if data is like this?

Then, that in itself is a change to the initial requirements, which mind you are more limited than my hard coded values.;-) The sum of the values indicate a certain sequence could be another hint there who knows?

It all boils down to the basics, if you are looking for a proper answer, ask a proper question!

😎

Sean Lange SSC Guru Points: 286573 More actions · Answer 9

ZZartin (8/27/2014)
WITH TEMP_CTE AS (
SELECT steps, ROW_NUMBER() OVER(ORDER BY steps) AS ROW_NUM FROM #table1
)
SELECT t1.steps, t2.steps FROM TEMP_CTE t1, TEMP_CTE t2
WHERE t1.ROW_NUM < (SELECT MAX(ROW_NUM) FROM TEMP_CTE)
AND (t2.ROW_NUM - 1) = t1.ROW_NUM

This will work but the code posted by rxm119528 is much simpler.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 10

Quick question, what version and edition of sql server (select @@version) are you on?

😎

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Why did people post code? It would have been nice to see what the OP had tried first since this looks like homework even though the OP denies it.

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 12

Lynn Pettis (8/27/2014)
Why did people post code? It would have been nice to see what the OP had tried first since this looks like homework even though the OP denies it.

+1 It's too basic to be a workplace issue. It's TSQL 101.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 13

Lynn Pettis (8/27/2014)
Why did people post code? It would have been nice to see what the OP had tried first since this looks like homework even though the OP denies it.

Sorry about that, guess I'm a little trigger happy these days

😎

keshabsingh SSC Veteran Points: 232 More actions · Answer 14

Thanks Eirikur Eiriksson!.

Your attempt is not bad. The approaches other users presented are more robust and accurate. Thanks anyway.