Sqlpivot get part of data nad select as rows column

You don't need a PIVOT for this, but you can do it with a Common Table Expression.

The first problem you have is that you have not defined how you've decided that the values from 2000

2000	200	456	45	35

should be displayed on the same line as

2001	300	4	43	545

It looks as if you are using "line 1 from 2000 goes with line 1 of 2001 and line 1 of 2002" etc, but you have nothing to defined what is "line 1", "line 2" of each group - you really need some sort of ID column on your base table.

I created your sample data with this:

create table #radhasapna ([Year] int, Q int, Q2 int, Q3 int, Q4 int)
insert into #radhasapna ([Year] , Q , Q2 , Q3 , Q4 ) values
(2000,	200,	456,	45,	35	),
(2000,	200,	435,	435,	545	),
(2000,	200,	454,	43,	45	),
(2000,	200,	45,	435,	4	),
(2001,	300,	4,	43,	545	),
(2001,	300,	43,	4,	56	),
(2001,	300,	4,	4,	4	),
(2001,	300,	43,	43,	6	),
(2001,	300,	4,	56,	34	),
(2002,	400,	4,	34,	43	),
(2002,	400,	43,	45,	6	),
(2002,	400,	4,	45,	4	),
(2002,	400,	43,	34,	54	),
(2002,	400,	45,	45,	6)

From there I was able to get the results you wanted by giving each row in each year a ROW_NUMBER() and then just doing multiple joins to the results.

;with cte as
(
	SELECT ROW_NUMBER() OVER (PARTITION BY [Year] ORDER BY [Year]) as r, 
		[Year], Q, Q2, Q3, Q4
	FROM #radhasapna
)
select a.*, b.*, c.*
from cte a 
left outer join cte b on a.[Year] = b.[Year] and a.r = b.r
left outer join cte c on a.[Year] = c.[Year] and a.r = c.r

I had to put an ORDER BY into the Window function but a constant is not allowed, so I used [Year] again ... which means the fact I got the correct results is pure good luck!

BUT if I (you!) define the base table like this

create table #radhasapna (id int identity(1,1), [Year] int, Q int, Q2 int, Q3 int, Q4 int)

and use

;with cte as
(
	SELECT ROW_NUMBER() OVER (PARTITION BY [Year] ORDER BY id) as r,
		 [Year], Q, Q2, Q3, Q4
	FROM #radhasapna
)
select a.*, b.*, c.*
from cte a 
left outer join cte b on a.[Year] = b.[Year] and a.r = b.r
left outer join cte c on a.[Year] = c.[Year] and a.r = c.r

then your results are guaranteed

-----------------------------------------------------------------------------
[EDIT] - OP Pointed out that the number of years could vary.
-----------------------------------------------------------------------------

You will need to use dynamic SQL if the number of years could vary, something like this:
1. Set up some initial values, the "smallest" year (@minY) and the number of years (@years) and the basis for our dynamic sql (@sql)

declare @years integer = (SELECT COUNT(DISTINCT [Year]) FROM #radhasapna)
declare @minY nvarchar(10) = (SELECT CAST(MIN([Year]) as nvarchar) FROM #radhasapna)

declare @sql nvarchar(max)
set @sql = ';with cte as (
	SELECT ROW_NUMBER() OVER (PARTITION BY [Year] ORDER BY id) as r,
		 [Year], Q, Q2, Q3, Q4
	FROM #radhasapna )
SELECT '

2. Replace what I had earlier as

select a.*, b.*, c.*

with

select a1.*, a2.*, a3.*, etc

Note I'm now using a1, a2, a3, etc as the table aliases...

declare @i int = 1
WHILE @i <= @years
begin
	set @sql = @sql + stuff('aXX', 2,2, @i) + '.*'
	if @i < @years
	begin
		set @sql = @sql + ','
	end 
	set @i = @i + 1
end

If I PRINT @sql at this stage I get this

;with cte as (
	SELECT ROW_NUMBER() OVER (PARTITION BY [Year] ORDER BY id) as r,
		 [Year], Q, Q2, Q3, Q4
	FROM #radhasapna )
SELECT a1.*,a2.*,a3.*,a4.*,a5.*

3. Work out how to do the joins...

set @sql = @sql + ' from cte a1 '

set @i = 2
WHILE @i <= @years
begin
	declare @s nvarchar(10) = stuff('aXX', 2,2, @i)
	declare @prevS nvarchar(10) = stuff('aXX',2,2,@i-1)
	set @sql = @sql + 'left outer join cte ' + @s + ' on ' + @s + '.[Year] = ' + @prevS + '.[Year] + 1 and a1.r = ' + @s + '.r '
	set @i = @i + 1
end

Again, if I PRINT @sql now then this has been added to the end

from cte a1 
left outer join cte a2 on a2.[Year] = a1.[Year] + 1 and a1.r = a2.r 
left outer join cte a3 on a3.[Year] = a2.[Year] + 1 and a1.r = a3.r 
left outer join cte a4 on a4.[Year] = a3.[Year] + 1 and a1.r = a4.r 
left outer join cte a5 on a5.[Year] = a4.[Year] + 1 and a1.r = a5.r 

4. All that is left to do is put a WHERE clause and an ORDER BY clause on the end of the sql and run it

set @sql = @sql + 'where a1.[Year] = ' + @minY + ' order by a1.r'

EXEC sp_executesql @sql