Change the sql table output - Rows to Columns I have a result from recursive query that gives me Table1 (SQL Server 2008 R2).
I need to convert the Table1 to Table2 and I don't know the best and simplest approach to do it.
Any suggestions?
Table1

Table2

Regards.
I found a very simple way of doing this. It might be not pretty but it's good enough for me!
-- Delete temporary tables
If OBJECT_ID('tempdb..#tempTable0') Is Not Null DROP TABLE #tempTable0
If OBJECT_ID('tempdb..#tempTable1') Is Not Null DROP TABLE #tempTable1
If OBJECT_ID('tempdb..#tempTable2') Is Not Null DROP TABLE #tempTable2
If OBJECT_ID('tempdb..#tempTable3') Is Not Null DROP TABLE #tempTable3

-- Add a row number to each record
SELECT *, ROW_NUMBER() OVER(ORDER BY (Select 0)) AS Row_Num
INTO #tempTable0
FROM Table1

-- Copy each record into a temporary table; Add 1 to be able join the temporary tables
Select Id as ID_1, Name as Name_1, 1 AS Row_Num Into #tempTable1 From #tempTable0 where Row_Num = 1
Select Id as ID_2, Name as Name_2, 1 AS Row_Num Into #tempTable2 From #tempTable0 where Row_Num = 2
Select Id as ID_3, Name as Name_3, 1 AS Row_Num Into #tempTable3 From #tempTable0 where Row_Num = 3

-- Final Record
Select ID_1, Name_1, ID2, Name_2, ID_3, Name_3
From
    #tempTable1 as T1 Left Join
    #tempTable2 as T2 On T2.Row_Num = T1.Row_Num Left Join
    #tempTable3 as T3 On T3.Row_Num = T1.Row_Num