IF object_id('tempdb..#temp01') IS NOT NULL DROP TABLE #temp01 SELECT *, IDENTITY(int,1,1) AS pk INTO #temp01 FROM mytable --SELECT * FROM #temp01 SELECT (SELECT col1 FROM #temp01 WHERE pk=1) AS NewCol1, (SELECT col1 FROM #temp01 WHERE pk=2) AS NewCol2, (SELECT col1 FROM #temp01 WHERE pk=3) AS NewCol3 UNION ALL SELECT (SELECT col2 FROM #temp01 WHERE pk=1) AS NewCol1, (SELECT col2 FROM #temp01 WHERE pk=2) AS NewCol2, (SELECT col2 FROM #temp01 WHERE pk=3) AS NewCol3 UNION ALL SELECT (SELECT col3 FROM #temp01 WHERE pk=1) AS NewCol1, (SELECT col3 FROM #temp01 WHERE pk=2) AS NewCol2, (SELECT col3 FROM #temp01 WHERE pk=3) AS NewCol3 UNION ALL SELECT (SELECT col4 FROM #temp01 WHERE pk=1) AS NewCol1, (SELECT col4 FROM #temp01 WHERE pk=2) AS NewCol2, (SELECT col4 FROM #temp01 WHERE pk=3) AS NewCol3This code is not optimized but should give you a starting point.