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.
3
-
Thanks for the tip, but I didn't now how to do it with Pivot!Elio Fernandes– Elio Fernandes2020-07-17 10:14:37 +00:00Commented Jul 17, 2020 at 10:14
-
Can you please clarify the required logic? Can we assume that the numerical postfixes in the ID column will always be -1, -2, -3? Or could there be an unknown number of such instances?Eitan Blumin– Eitan Blumin2020-07-17 14:56:37 +00:00Commented Jul 17, 2020 at 14:56
-
There is no logic for the column names! The names are whatever you want. I made a recursive query over a table to get a hierarchical tree and the result was in a column and I needed it in a single line.So I came up using temporary tables because I was not able to do it with Pivot. Later I end up making a function and had to replace the temporary tables with table variables. In the example I was looking for just 3 instances but It can be done any number of instances.Elio Fernandes– Elio Fernandes2020-07-20 22:22:50 +00:00Commented Jul 20, 2020 at 22:22
Add a comment
|
1 Answer
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