SQL Subquery help

[~J~]

Hi,

I was wondering if someone could help me achieve a view I'm after creating.

I currently have a table which is as follows...

	P	44100	SAL	ADM	2005	11	1000	200
	P	44100	SAL	ADM	2005	10	500	90
	P	44100	SAL	ADM	2005	9	75	34
	P	44100	SAL	ADM	2004	11	800	1300
	P	44100	SAL	ADM	2004	10	500	400
	P	44100	SAL	ADM	2004	9	50	100

The last 2 columns are of interest to me. What I want to achieve (using the above as an example) are 3 rows which read as follows:

11	1000	200     800     1300
10	500	90       500     400
9	75	34         50      100

What I'm basically trying to achieve is a view that has the last 2 columns for the current year and all periods, with the last 2 columns of the previous year (and relating period) added to the end.

I really can't get my head around how to do this, but I'm sure it can be done!

Any help I'd really appreciate it.

TIA

[MAK]

--Next time instead of pasting the data, paste the insert statement for the table. it will save a lot of time.

create table sub(col1 int, col2 int, col3 int,col4 int)
insert into sub select 2005, 11, 1000, 200
insert into sub select 2005, 10, 500, 90
insert into sub select 2005, 9, 75, 34
insert into sub select 2004, 11, 800, 1300
insert into sub select 2004, 10, 500, 400
insert into sub select 2004, 9, 50, 100


select a.col1,a.col2,a.col3,a.col4,
(select b.col3 from sub b where a.col2=b.col2 and b.col1=a.col1-1) as col5,
(select b.col4 from sub b where a.col2=b.col2 and b.col1=a.col1-1) as col6
from sub a where a.col1=year(getdate())