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

• Sean Lange

  SSC Guru

  Points: 286601

  More actions

  August 15, 2013 at 12:03 pm

  #1641890

  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: 183706

  More actions

  August 15, 2013 at 12:10 pm

  #1641893

  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: 61141

  More actions

  August 15, 2013 at 12:19 pm

  #1641899

  -- (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