result set conversion - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

result set conversion

Author

Topic

Sql_forum
Yak Posting Veteran

50 Posts

Posted - 2013-10-15 : 06:03:34

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

169 Posts

Posted - 2013-10-15 : 06:46:47

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

Sql_forum
Yak Posting Veteran

50 Posts

Posted - 2013-10-15 : 07:15:59

Hi Veeru,

My result set not static, it will vary from time to time

Sql_forum
Yak Posting Veteran

50 Posts

Posted - 2013-10-15 : 07:30:38

Hi Veeru,

Thanks for your reply, it helped me to apply logic for my req.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-16 : 03:04:28

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources