Data rearrange to get delta value between years in SQL


set nocount on;

use

MSDN_forum;

if

object_id(‘dbo.table1’) is not null drop table dbo.table1;

CREATE

TABLE table1 (ID int identity(1,1), myid int, myYear int, val int)

insert

into table1

SELECT

1, 2000, 22

UNION

ALL SELECT 1, 2001, 33

UNION

ALL SELECT 1, 2002, 44

UNION

ALL SELECT 1, 2003, 55

UNION

ALL SELECT 1, 2004, 66

UNION

ALL SELECT 1, 2005, 77

UNION

ALL SELECT 1, 2006, 88

UNION

ALL SELECT 2, 2000, 11

UNION

ALL SELECT 2, 2001, 33

UNION

ALL SELECT 2, 2002, 44

UNION

ALL SELECT 2, 2003, 55

UNION

ALL SELECT 2, 2004, 66

UNION ALL SELECT 2, 2005, 77

UNION

ALL SELECT 2, 2006, 88

select

a.id,a.myid,a.myYear,a.val

,

b.val,c.val,d.val,e.val,f.val,g.val

FROM

(select id,myid, myYear,val from table1) a

left

join (select id, myid, myYear,val from table1) b

ON

a.myid=b.myid AND a.myYear=b.myYear1

Left

join (select id, myid, myYear,val from table1) c

ON

a.myid=c.myid AND a.myYear=c.myYear2

Left

join (select id, myid, myYear,val from table1) d

ON

a.myid=d.myid AND a.myYear=d.myYear3

Left

join (select id, myid, myYear,val from table1) e

ON

a.myid=e.myid AND a.myYear=e.myYear4

Left

join(select id, myid, myYear,val from table1) f

ON

a.myid=f.myid AND a.myYear=f.myYear5

Left

join (select id, myid, myYear,val from table1) g

ON

a.myid=g.myid AND a.myYear=g.myYear6

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s