Matrix transformation in SQL server 2014

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?