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.
Advertisement
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.