15,567,385 members
See more:
i have a table like this: 3 columns * 4 rows

col1 col2 col3
a b c
d e f
g h i
i j k

i want transfer it by sql to a 4 columns * 3 rows table.
col1 col2 col3 col4
a d g i
b e h j
c f i k

is there an elegant way to do this ?

many thanks~!!!

What I have tried:

i tried pivot and unpivot, doesn't help.
Posted
Updated 4-Jun-17 20:44pm
v2

## Solution 1

Hi Matrimony,

You can use `pivot`, and `unpivot` for this problem. You can use the following query:
SQL
```select * from (
select *, ROW_NUMBER() over (partition by name order by name) as rownum from [dbo].[TableName]
unpivot(value for name in (col1, col2, col3))temp1)temp2
pivot (max(value) for rownum in ([1], [2], [3], [4]))temp3```
Let me explain a bit.

At first you are unpivoting - means you are transposing the columns to rows. So your table looks like the following at this point:
```value	name	rownum
a	    col1	  1
d	    col1	  2
g	    col1	  3
j	    col1	  4
k	    col2	  1
h	    col2	  2
e	    col2	  3
b	    col2	  4
c	    col3	  1
f	    col3	  2
i	    col3	  3
l	    col3	  4```
Why do we need the additional column `rownum`? It is because we shall use `pivot` (to transform rows into columns) in our next line and `pivot` requires to apply an aggregate function on a column. The following line transposes the columns (named 'values' hereby) as rows by pivoting on the `rownum` variable.
`pivot (max(value) for rownum in ([1], [2], [3], [4]))q`
Now your final output looks like the following:
```name	1	2	3	4
col1	a	d	g	j
col2	k	h	e	b
col3	c	f	i	l```
Hope this helps?

v2