Split a column into 3 columns?

Question

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 1

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

Split a column into 3 columns?

Sign up