Click here to Skip to main content
15,567,385 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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.
Updated 4-Jun-17 20:44pm

1 solution

Hi Matrimony,

You can use pivot, and unpivot for this problem. You can use the following query:
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?
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900