how to output as follows - 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)

how to output as follows

Author

Topic

udaymahajan
Starting Member

17 Posts

Posted - 2009-04-28 : 09:48:04

My table is as follows

table name : YearRecord,
and columns are Years,Population

data inside on that table is

YearRecord
===================
Years Population
2001 1000
2002 500
2003 2000
2004 3000

now i want to display output as follows

Years Ratio
2002-2001 -500
2003-2002 1500
2004-2003 1000

so how can i design Sql Query to show this output

uday

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-28 : 09:54:47

[code]DECLARE @Sample TABLE
(
Years SMALLINT,
Population INT
)

INSERT @Sample
SELECT 2001, 1000 UNION ALL
SELECT 2002, 500 UNION ALL
SELECT 2003, 2000 UNION ALL
SELECT 2004, 3000

-- Peso
SELECT aYear,
MAX(bYear),
SUM(pop)
FROM (
SELECT Years AS aYear,
NULL AS bYear,
Population AS pop
FROM @Sample

UNION ALL

SELECT Years + 1 AS aYear,
Years AS bYear,
-Population AS pop
FROM @Sample
) AS d
GROUP BY aYear
HAVING COUNT(*) = 2
ORDER BY aYear[/code]

E 12°55'05.63"
N 56°04'39.26"

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-28 : 09:59:19

Peso are there any shortcomings if a query like this is used?

declare @Table_X table
(
Years varchar(10),
Pop int
)
Insert into @Table_X
select 2001 ,1000 union all
select 2002 ,500 union all
select 2003 ,2000 union all
select 2004 ,3000

select t1.Years + '-' + t2.Years as Years,t1.Pop-t2.Pop as Ratio from @Table_X t1
inner join @Table_X t2 on t1.Years=t2.Years+1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-28 : 10:02:47

There could be a performance penalty if the number of records are large.
It takes some CPU to build the hash table for joining.

See this topic for comparison for JOIN and UNION ALL with moving average
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=93911

E 12°55'05.63"
N 56°04'39.26"

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-28 : 10:29:41

quote:
Originally posted by Peso

There could be a performance penalty if the number of records are large.
It takes some CPU to build the hash table for joining.

See this topic for comparison for JOIN and UNION ALL with moving average
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=93911

E 12°55'05.63"
N 56°04'39.26"

It does works faster.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources