Solution to TSQL Challenge 30 - A Matrix Transposition challenge in TSQL By gyankoju

SELECT id
,[1] col1
,[2] col2
,[3] col3
,[4] col4
,[5] col5
FROM 
(
	 select id,y,val,row_number() OVER (partition by id,y ORDER BY val) x
  FROM 
	(
	SELECT id,row_number() OVER (partition by id ORDER BY col1) y,col1 val FROM TC30_Metrics tm
	UNION ALL
	SELECT id,row_number() OVER (partition by id ORDER BY col2) y,col2 val FROM TC30_Metrics tm
	UNION ALL
	SELECT id,row_number() OVER (partition by id ORDER BY col3) y,col3 val FROM TC30_Metrics tm
	UNION ALL
	SELECT id,row_number() OVER (partition by id ORDER BY col4) y,col4 val FROM TC30_Metrics tm
	UNION ALL
	SELECT id,row_number() OVER (partition by id ORDER BY col5) y,col5 val FROM TC30_Metrics tm
	) rar_data
) cross_data
PIVOT
(
max(val)
FOR y IN
( [1], [2], [3], [4], [5] )
) AS pvt
ORDER BY id,x

Performance stats of the above solution

Sr# StartTime                 Reads      Writes         CPU Duration 
--- ------------------- ----------- ----------- ----------- -------- 
  1 Sep 13 2010 10:50AM        1796           0        1263    5.895
  2 Sep 13 2010 11:00AM        1796           0        1170    2.030
  3 Sep 13 2010 11:11AM        1796           0        1155    1.689
  4 Sep 13 2010 11:21AM        1796           0        1108    1.611
  5 Sep 13 2010 11:31AM        1796           0        1045    1.632