SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 result set conversion
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Sql_forum
Yak Posting Veteran

India
50 Posts

Posted - 10/15/2013 :  06:03:34  Show Profile  Reply with Quote
Hi

can some body tell me , how toconvert the below set

2008
2009
2010
2011
2012
2013
2014

into
2008-2009
2009-2010
2010-2011
.......
2013-2014

VeeranjaneyuluAnnapureddy
Posting Yak Master

India
169 Posts

Posted - 10/15/2013 :  06:46:47  Show Profile  Reply with Quote
Declare @Te table(Id int)

Insert @Te
Select 2008
union all Select 2009
union all Select 2010
union all Select 2011
union all Select 2012
union all Select 2013
union all Select 2014

Declare @T1 Table(Id int,Seq int)

Insert @T1
Select Id,row_number() Over(Order By Id) As Seq From @Te

Declare @T2 table(Id int,Seq int)

Insert @T2
Select Id,row_number() Over(Order By Id)+1 as Seq From @Te

Select Convert(Varchar(20),t2.Id)+' - '+ Convert(Varchar(20),t1.Id) AS 'Date'
From @T1 as t1
Inner Join @T2 as t2
on t1.seq = t2.seq

Edited by - VeeranjaneyuluAnnapureddy on 10/15/2013 06:49:42
Go to Top of Page

Sql_forum
Yak Posting Veteran

India
50 Posts

Posted - 10/15/2013 :  07:15:59  Show Profile  Reply with Quote
Hi Veeru,

My result set not static, it will vary from time to time
Go to Top of Page

Sql_forum
Yak Posting Veteran

India
50 Posts

Posted - 10/15/2013 :  07:30:38  Show Profile  Reply with Quote
Hi Veeru,

Thanks for your reply, it helped me to apply logic for my req.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/16/2013 :  03:04:28  Show Profile  Reply with Quote
isnt it just a matter of this?

;With CTE
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY yr) AS Seq,Yr
FROM table
)

SELECT CAST(c1.Yr AS varchar(4)) + '-' + CAST(c2.Yr AS varchar(4))
FROM CTE c1
JOIN CTE c2
ON c2.Seq = c1.Seq + 1
AND c1.Seq % 2 > 0


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000