Pfthb

There are two tables:

First table has two columns with the following data:

[col1] [RowIndex]
apple 1
10 2
red 3
pear 4
20 5 
yellow 6

Second empty table has 3 columns:

[FruitType] [Weight] [Color] 

I wish to load the first table's [col1] data into the second one but "rotated" and without any aggregation.

Result would would look like this:

[FruitType] [Weight] [Color] 
apple 10 red
pear 20 yellow

How can i insert data to the second table? I tried PIVOT but cannot wrap my head around.

A conditional aggregation in concert with row_number() should do the trick

Example

Declare @YourTable Table ([RowIndex] int,[col1] varchar(50))
Insert Into @YourTable Values 
 (1,'apple')
,(2,'1')
,(3,'red')
,(4,'pear')
,(5,'2')
,(6,'yellow')

Select [FruitType] = max(case when col=1 then Col1 end)
 ,[Weight] = max(case when col=2 then Col1 end)
 ,[Color] = max(case when col=0 then Col1 end)
 From (
 Select *
 ,Grp = (Row_Number() over (order by [RowIndex]) - 1) / 3
 ,Col = Row_Number() over (order by [RowIndex]) % 3
 From @YourTable
 ) A
 Group By Grp

Returns

FruitType Weight Color
apple 1 red
pear 2 yellow

NOTE:

If RowIndex is truly sequential, you can remove the row_number() function and simply use RowIndex

You can try to use ROW_NUMBER window function with some calculation

insert into SecondTable ( FruitType,Weight,Color)
SELECT FruitType,Weight,Color
FROM (
 SELECT MAX(CASE WHEN rn % 3 = 1 THEN [col1] END) FruitType,
 MAX(CASE WHEN rn % 3 = 2 THEN [col1] END) Weight,
 MAX(CASE WHEN rn % 3 = 0 THEN [col1] END) Color
 FROM (
 SELECT *,ROW_NUMBER() OVER(ORDER BY RowIndex) rn
 FROM FirstTable
 ) tt
 GROUP BY (rn - 1) /3
)tt

sqlfiddle