I have table 1 like this:
----------------
| id | col |
----------------
| 1 | 7 |
| 2 | 6 |
| 3 | 1 |
| 4 | 8 |
| 5 | 9 |
| 6 | 5 |
| 7 | 4 |
| 8 | 3 |
| 9 | 2 |
----------------
col
-------------------
| A | B | C |
-------------------
| 7 | 6 | 1 |
| 8 | 9 | 5 |
| 4 | 3 | 2 |
-------------------
Assuming the ID is incremental
Declare @Table table (id int,col int)
Insert Into @Table values
(1,7),
(2,6),
(3,1),
(4,8),
(5,9),
(6,5),
(7,4),
(8,3),
(9,2)
Select ColA=max(case when ID % 3 = 1 then col else 0 end)
,ColB=max(case when ID % 3 = 2 then col else 0 end)
,ColC=max(case when ID % 3 = 0 then col else 0 end)
From @Table
Group By ceiling(ID/3.0)
Returns
ColA ColB ColC
7 6 1
8 9 5
4 3 2