Convert Columns into Rows - 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 2005 Forums

Transact-SQL (2005)

Convert Columns into Rows

Author

Topic

gavinjb
Starting Member

34 Posts

Posted - 2008-11-05 : 03:49:31

Hi,

I have a table with the following data

ID StartYear EndYear
1 2006 2008

I need to write some SQL that will output this as rows of data like the following

ID Year
1 2006
1 2007
1 2008

Can anyone help with writing a query to perform this.

Thanks,

Gavin,

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-11-05 : 04:08:04

U need a Tally or Numbers table to do it.


DECLARE @T TABLE (ID INT, StartYear INT, EndYear INT)
INSERT INTO @T
SELECT 1, 2006, 2008

SELECT T.Id, M.Number AS 'Year'
FROM @T T
INNER JOIN Master..spt_Values M ON M.Number BETWEEN T.StartYear AND T.EndYear
WHERE M.Type = 'P'

"There is only one difference between a dream and an aim. A dream requires soundless sleep to see, whereas an aim requires sleepless efforts to achieve..!!"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-05 : 04:14:31

[code]
declare @t table(ID int,StartYear int,EndYear int)
insert into @t
select 1 ,2006 ,2008

select t1.id,t2.number from @t as t1 inner join
(
select row_number() over(order by s1.name) as number from sysobjects s1 cross join sysobjects as s2
) as t2 on t2.number between t1.StartYear and t1.EndYear

[/code]
Also look for UNPIVOT in sql server help file

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-05 : 04:15:14

Madhivanan

Failing to plan is Planning to fail

gavinjb
Starting Member

34 Posts

Posted - 2008-11-05 : 04:31:43

Thanks that worked perfectly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources