Need help in query...

Question

Need help in query...

raghavender.2369

SSC Eights!

Points: 833
More actions
August 15, 2013 at 11:42 am

#296997

Number
10
20
30
40
50
table has a column 'Number'
expected result is
Numer-range
10 - 20
20 - 30
30 - 40
need to diaplay as it is

Viewing 4 posts - 1 through 3 (of 3 total)

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

Sean Lange SSC Guru Points: 286589 More actions · Answer 1

raghavender.2369 (8/15/2013)
Number
10
20
30
40
50
table has a column 'Number'
expected result is
Numer-range
10 - 20
20 - 30
30 - 40
need to diaplay as it is

Are the values always 10 - 20, 20 - 30 or are the real values more dynamic and you need to read the table?

If the values are consistent you might look at using a tally table. If they are dynamic then a recursive cte is likely a decent method.

_______________________________________________________________

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/

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

With SQL 2012 you could use LEAD, however, you need a self join on previous versions.

WITH Sample_Data(Number) AS(

SELECT 10 UNION ALL

SELECT 20 UNION ALL

SELECT 30 UNION ALL

SELECT 40 UNION ALL

SELECT 50

),

CTE AS(

SELECT Number,

ROW_NUMBER() OVER( ORDER BY Number) rn

FROM Sample_Data

)

SELECT CAST( a.Number AS varchar(10))+ ' - ' + CAST( b.Number AS varchar(10)) AS Number_Range

FROM CTE a

JOIN CTE b ON a.rn = b.rn - 1

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Alan Burstein SSC Guru Points: 61136 More actions · Answer 3

-- (1) Source Data

DECLARE @nbrs TABLE (n int primary key);

INSERT @nbrs VALUES (10),(20),(30),(40),(50);

-- (2) Solution

WITH

s1 AS (SELECT ROW_NUMBER() OVER (ORDER BY n) AS rn,n FROM @nbrs),

s2 AS (SELECT rn-1 rn, n FROM s1)

SELECT CAST(s1.n AS varchar(3))+' - '+CAST(s2.n AS varchar(3)) AS Number_range

FROM s1

JOIN s2 ON s1.rn=s2.rn;

Edit: did not notice Louis' post :doze:

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001