How can create a new table from another after Calculate Some Field

I have a table called WorkSpace with 4 columns.

Each record in this table refers to their parent record through the Parent_id column.

You should continue this cycle until you reach the main parent and all of these steps specified in the View_Parent_id column.

The ID of main parent number is specified by 1.

So now I want create a NEW_WorkSpace table and separate each of the values in View_Parent_id column into a separate column.

The View_Parent_id column is ultimately 15 value so we need 15 columns in new table (Level_1…Level_15).

I used the loop in my query but it was very slow.

WorkSpace table has 1.5 million rows.

WorkSpace table:

+-----+-------+-----------+------------------+
| W_ID| Title | Parent_id | View_Parent_id   |
+-----+-------+-----------+------------------+
| 1   |   AAA |    null   | null             |
| 2   |   BV  |    1      | 1                |
| 3   |   CX  |    2      | 1+2              |
| 4   |   DSO |    2      | 1+2              |
| 5   |   ER  |    3      | 1+2+3            |
| 6   |   ER  |    5      | 1+2+3+5          |
| ... |  ...  |    ...    | ...              |
| 1000|   MNV |    1      | 1                |
| 1001|   SF  |    1000   | 1+1000           |
| 1002|   EDD |    1000   | 1+1000           |
| 1003|   YSG |    1001   | 1+1000+1001      |
| 1004|   RPO |    1003   | 1+1000+1001+1003 |
+-----+-------+-----------+------------------+

NEW_WorkSpace table:

+-----+-------+-----------+---------+---------+---------+-----+----------+
| ID  |  W_id | Parent_id | Level_1 | Level_2 | Level_3 | ... | Level_15 |
+-----+-------+-----------+---------+-------- +---------+-----+----------+
| 100 |   1   |    null   | AAA     |         |         | ... |          |
| 101 |   2   |    1      | AAA     |  BV     |         | ... |          |
| 102 |   3   |    2      | AAA     |  BV     |         | ... |          |
| 103 |   4   |    2      | AAA     |  BV     |  CX     | ... |          |
| 104 |   5   |    3      | AAA     |  BV     |  CX     | ... |          |
| ... |   ... |    ...    | ...     |  ...    |  ...    | ... |  ...     |
+-----+-------+-----------+---------+---------+---------+-----+----------+

My code:

BEGIN    
    DECLARE @W_ID decimal(20, 0);
    DECLARE @parent_id decimal(20, 0);

    DECLARE @Level1 nvarchar(MAX);
    DECLARE @Level2 nvarchar(MAX);
    DECLARE @Level3 nvarchar(MAX);
    DECLARE @Level4 nvarchar(MAX);
    DECLARE @Level5 nvarchar(MAX);
    DECLARE @Level6 nvarchar(MAX);
    DECLARE @Level7 nvarchar(MAX);
    DECLARE @Level8 nvarchar(MAX);
    DECLARE @Level9 nvarchar(MAX);
    DECLARE @Level10 nvarchar(MAX);
    DECLARE @Level11 nvarchar(MAX);
    DECLARE @Level12 nvarchar(MAX);
    DECLARE @Level13 nvarchar(MAX);
    DECLARE @Level14 nvarchar(MAX);
    DECLARE @Level15 nvarchar(MAX);
    DECLARE @titles_tmp nvarchar(MAX);

    DECLARE @cont_spilit_tittle int;
    DECLARE @parent_titles_tmp nvarchar(MAX);
    DECLARE @cont_tmp int;
    DECLARE @cont int;

    SELECT @cont = COUNT(*) FROM dbo.WorkSpace ;
    SET @cont_tmp = 0;

    WHILE (@cont_tmp < @cont)
    BEGIN  
        SET @W_ID = (SELECT dbo.WorkSpace.W_ID FROM dbo.WorkSpace  
                     ORDER BY W_ID ASC OFFSET @cont_tmp ROWS FETCH NEXT 1 ROWS ONLY)
        SET @parent_id = (SELECT dbo.WorkSpace.parent_id FROM dbo.WorkSpace 
                          ORDER BY W_ID ASC OFFSET @cont_tmp ROWS FETCH NEXT 1 ROWS ONLY)
        SET @titles_tmp = (SELECT dbo.WorkSpace.title FROM dbo.WorkSpace 
                           ORDER BY W_ID ASC OFFSET @cont_tmp ROWS FETCH NEXT 1 ROWS ONLY)
        SET @parent_titles_tmp = (SELECT dbo.WorkSpace.parent_titles FROM dbo.WorkSpace 
                                  ORDER BY W_ID ASC OFFSET @cont_tmp ROWS FETCH NEXT 1 ROWS ONLY)

        IF OBJECT_ID('tempdb..#MyTempTable') IS NOT NULL 
             DROP TABLE #MyTempTable

        SELECT IDENTITY(INT, 1, 1) AS 'RowID', * 
        INTO #MyTempTable 
        FROM StringSplitXML(@parent_titles_tmp, '+')

        INSERT INTO #MyTempTable 
        VALUES (@titles_tmp)

        SET @cont_spilit_tittle = (SELECT COUNT(*) FROM #MyTempTable)

        IF(@cont_spilit_tittle < 0)
            SET @cont_spilit_tittle = 1

        WHILE (@cont_spilit_tittle < 15)
        BEGIN
            INSERT INTO #MyTempTable VALUES ('')
            SET @cont_spilit_tittle = CAST(@cont_spilit_tittle AS INT) + 1
        END

        SET @Level1 = (SELECT Value FROM #MyTempTable 
                       ORDER BY RowID ASC OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY)
        SET @Level2 = (SELECT Value FROM #MyTempTable 
                       ORDER BY RowID ASC OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY)
        SET @Level3 = (SELECT Value FROM #MyTempTable 
                       ORDER BY RowID ASC OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY)
        SET @Level4 = (SELECT Value FROM #MyTempTable 
                       ORDER BY RowID ASC OFFSET 3 ROWS FETCH NEXT 1 ROWS ONLY)
        SET @Level5 = (SELECT Value FROM #MyTempTable 
                       ORDER BY RowID ASC OFFSET 4 ROWS FETCH NEXT 1 ROWS ONLY)
        SET @Level6 = (SELECT Value FROM #MyTempTable 
                       ORDER BY RowID ASC OFFSET 5 ROWS FETCH NEXT 1 ROWS ONLY)
        SET @Level7 = (SELECT Value FROM #MyTempTable 
                       ORDER BY RowID ASC OFFSET 6 ROWS FETCH NEXT 1 ROWS ONLY)
        SET @Level8 = (SELECT Value FROM #MyTempTable 
                       ORDER BY RowID ASC OFFSET 7 ROWS FETCH NEXT 1 ROWS ONLY)
        SET @Level9 = (SELECT Value FROM #MyTempTable 
                       ORDER BY RowID ASC OFFSET 8 ROWS FETCH NEXT 1 ROWS ONLY)
        SET @Level10 = (SELECT Value FROM #MyTempTable 
                        ORDER BY RowID ASC OFFSET 9 ROWS FETCH NEXT 1 ROWS ONLY)
        SET @Level11 = (SELECT Value FROM #MyTempTable 
                        ORDER BY RowID ASC OFFSET 10 ROWS FETCH NEXT 1 ROWS ONLY)
        SET @Level12 = (SELECT Value FROM #MyTempTable 
                        ORDER BY RowID ASC OFFSET 11 ROWS FETCH NEXT 1 ROWS ONLY)
        SET @Level13 = (SELECT Value FROM #MyTempTable 
                        ORDER BY RowID ASC OFFSET 12 ROWS FETCH NEXT 1 ROWS ONLY)
        SET @Level14 = (SELECT Value FROM #MyTempTable 
                        ORDER BY RowID ASC OFFSET 13 ROWS FETCH NEXT 1 ROWS ONLY)
        SET @Level15 = (SELECT Value FROM #MyTempTable 
                        ORDER BY RowID ASC OFFSET 14 ROWS FETCH NEXT 1 ROWS ONLY)


        INSERT INTO [].[dbo].[NEW_WorkSpace]
           ([W_ID], [parent_id],
            [Level1], [Level2], [Level3], [Level4], [Level5],
            [Level6], [Level7], [Level8], [Level9], [Level10],
            [Level11], [Level12], [Level13], [Level14], [Level15])
        VALUES (@W_ID, @parent_id,
                @Level1, @Level2, @Level3, @Level4, @Level5,
                @Level6, @Level7, @Level8, @Level9, @Level10,
                @Level11, @Level12, @Level13, @Level14, @Level15) 

        SET @cont_tmp = CAST(@cont_tmp AS INT) + 1
    END  

    RETURN
END

Thank you for your help.

Answer

I would approach this using string operations on view_parent_id. Then aggregating the final result:

with cte as (
      select w_id, parent_id, view_parent_id,
             0 as lev, convert(varchar(max), concat(view_parent_id, '+', w_id, '+')) as parents 
      from t
      union all
      select w_id, parent_id, view_parent_id,
             1 + lev, 
             convert(int, left(parents, charindex('+', parents) - 1)),
             stuff(parents, 1, charindex('+', parents), '')
      from cte
      where parents <> ''
     )
select w_id, parent_id, view_parent_id,
       max(case when lev = 1 then parent_title end) as title_1,
       max(case when lev = 2 then parent_title end) as title_2,
       max(case when lev = 3 then parent_title end) as title_3,
       max(case when lev = 4 then parent_title end) as title_4,
       max(case when lev = 5 then parent_title end) as title_5
from (select cte.*, t.title as parent_title, count(*) over (partition by cte.w_id) as cnt
      from cte join
           t
           on t.w_id = cte.parent
      where lev > 0
     ) cte
group by w_id, parent_id, view_parent_id;

Here is a db<>fiddle.

As for processing. Probably the most expensive part of the query is the aggregation after the recursive CTE. The recursive portion is not doing any joining, so it should be fairly fast (string operations can be slow).

The join to get the label is using correct types, so an index on w_id can be used.