Hanaka Hanaka - 4 months ago 6
SQL Question

Split a column into 3 columns?

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 |
----------------


I want to create new table get data from column
col
of table 1 and get the result like this:

-------------------
| A | B | C |
-------------------
| 7 | 6 | 1 |
| 8 | 9 | 5 |
| 4 | 3 | 2 |
-------------------


Any suggestions? Thanks a lot.

Answer

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
Comments