Click here to Skip to main content
15,868,121 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi All.

I have data like below. (Sample)

CREATE TABLE [dbo].[Test0001](
	[Guid] [nvarchar](50) NOT NULL,
	[Level] [tinyint] NOT NULL,
	[Parent_ID] [nvarchar](50) NULL,
	[Child_ID] [nvarchar](50) NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Test0001] ([Guid], [Level], [Parent_ID], [Child_ID]) VALUES (N'268FF0B4-7328-40C2-94F1-8F963D54D00A', 0, NULL, N'Parent_001')
GO
INSERT [dbo].[Test0001] ([Guid], [Level], [Parent_ID], [Child_ID]) VALUES (N'268FF0B4-7328-40C2-94F1-8F963D54D00A', 1, N'Parent_001', N'Child_001')
GO
INSERT [dbo].[Test0001] ([Guid], [Level], [Parent_ID], [Child_ID]) VALUES (N'268FF0B4-7328-40C2-94F1-8F963D54D00A', 1, N'Parent_001', N'Child_002')
GO
INSERT [dbo].[Test0001] ([Guid], [Level], [Parent_ID], [Child_ID]) VALUES (N'268FF0B4-7328-40C2-94F1-8F963D54D00A', 2, N'Child_001', N'Child_002.1')
GO
INSERT [dbo].[Test0001] ([Guid], [Level], [Parent_ID], [Child_ID]) VALUES (N'268FF0B4-7328-40C2-94F1-8F963D54D00A', 2, N'Child_001', N'Child_002.2')
GO
INSERT [dbo].[Test0001] ([Guid], [Level], [Parent_ID], [Child_ID]) VALUES (N'268FF0B4-7328-40C2-94F1-8F963D54D00A', 2, N'Child_002', N'Child_002.3')
GO
INSERT [dbo].[Test0001] ([Guid], [Level], [Parent_ID], [Child_ID]) VALUES (N'268FF0B4-7328-40C2-94F1-8F963D54D00A', 2, N'Child_002', N'Child_002.4')
GO
INSERT [dbo].[Test0001] ([Guid], [Level], [Parent_ID], [Child_ID]) VALUES (N'268FF0B4-7328-40C2-94F1-8F963D54D00A', 2, N'Child_002', N'Child_002.5')
GO
INSERT [dbo].[Test0001] ([Guid], [Level], [Parent_ID], [Child_ID]) VALUES (N'268FF0B4-7328-40C2-94F1-8F963D54D00A', 3, N'Child_002.1', N'Child_003.1')
GO
INSERT [dbo].[Test0001] ([Guid], [Level], [Parent_ID], [Child_ID]) VALUES (N'268FF0B4-7328-40C2-94F1-8F963D54D00A', 3, N'Child_002.1', N'Child_003.2')
GO
INSERT [dbo].[Test0001] ([Guid], [Level], [Parent_ID], [Child_ID]) VALUES (N'268FF0B4-7328-40C2-94F1-8F963D54D00A', 3, N'Child_002.3', N'Child_003.3')
GO
INSERT [dbo].[Test0001] ([Guid], [Level], [Parent_ID], [Child_ID]) VALUES (N'268FF0B4-7328-40C2-94F1-8F963D54D00A', 3, N'Child_002.4', N'Child_003.4')
GO
INSERT [dbo].[Test0001] ([Guid], [Level], [Parent_ID], [Child_ID]) VALUES (N'268FF0B4-7328-40C2-94F1-8F963D54D00A', 3, N'Child_002.5', N'Child_003.5')
GO



I want the above records in below format. after parent the next child should come.

CREATE TABLE [dbo].[Test0002](
	[Guid] [nvarchar](50) NOT NULL,
	[Level] [tinyint] NOT NULL,
	[Patent_ID] [nvarchar](50) NOT NULL,
	[Child_ID] [nvarchar](50) NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Test0002] ([Guid], [Level], [Patent_ID], [Child_ID]) VALUES (N'268FF0B4-7328-40C2-94F1-8F963D54D00A', 1, N'Parent_001', N'Child_001')
GO
INSERT [dbo].[Test0002] ([Guid], [Level], [Patent_ID], [Child_ID]) VALUES (N'268FF0B4-7328-40C2-94F1-8F963D54D00A', 2, N'Child_001', N'Child_002.1')
GO
INSERT [dbo].[Test0002] ([Guid], [Level], [Patent_ID], [Child_ID]) VALUES (N'268FF0B4-7328-40C2-94F1-8F963D54D00A', 3, N'Child_002.1', N'Child_003.1')
GO
INSERT [dbo].[Test0002] ([Guid], [Level], [Patent_ID], [Child_ID]) VALUES (N'268FF0B4-7328-40C2-94F1-8F963D54D00A', 3, N'Child_002.1', N'Child_003.2')
GO
INSERT [dbo].[Test0002] ([Guid], [Level], [Patent_ID], [Child_ID]) VALUES (N'268FF0B4-7328-40C2-94F1-8F963D54D00A', 2, N'Child_001', N'Child_002.2')
GO
INSERT [dbo].[Test0002] ([Guid], [Level], [Patent_ID], [Child_ID]) VALUES (N'268FF0B4-7328-40C2-94F1-8F963D54D00A', 2, N'Child_002', N'Child_002.3')
GO
INSERT [dbo].[Test0002] ([Guid], [Level], [Patent_ID], [Child_ID]) VALUES (N'268FF0B4-7328-40C2-94F1-8F963D54D00A', 3, N'Child_002.3', N'Child_003.3')
GO
INSERT [dbo].[Test0002] ([Guid], [Level], [Patent_ID], [Child_ID]) VALUES (N'268FF0B4-7328-40C2-94F1-8F963D54D00A', 3, N'Child_002.3', N'Child_003.3')
GO
INSERT [dbo].[Test0002] ([Guid], [Level], [Patent_ID], [Child_ID]) VALUES (N'268FF0B4-7328-40C2-94F1-8F963D54D00A', 2, N'Child_002', N'Child_002.4')
GO
INSERT [dbo].[Test0002] ([Guid], [Level], [Patent_ID], [Child_ID]) VALUES (N'268FF0B4-7328-40C2-94F1-8F963D54D00A', 3, N'Child_002.4', N'Child_003.4')
GO
INSERT [dbo].[Test0002] ([Guid], [Level], [Patent_ID], [Child_ID]) VALUES (N'268FF0B4-7328-40C2-94F1-8F963D54D00A', 2, N'Child_002', N'Child_002.5')
GO
INSERT [dbo].[Test0002] ([Guid], [Level], [Patent_ID], [Child_ID]) VALUES (N'268FF0B4-7328-40C2-94F1-8F963D54D00A', 3, N'Child_002.5', N'Child_003.5')
GO



Please help me this.

What I have tried:

With CTE I found some examples in online but I couldn't use CTE in my code.
Posted
Updated 17-Aug-22 2:31am
Comments
CHill60 1-Aug-22 9:01am    
Why could you not use CTE?

1 solution

TLDR? Skip to the last 2 code sections for the final solution, or the 2nd code section for the proper solution.

I can't see any good reason why you can't use CTE unless your database version is incredibly out of date. If so you should consider an upgrade.

What may have happened is an error message "Incorrect syntax near the keyword 'with'. " - that is easily overcome by ensuring you terminate your statements with a semi-colon. I often use
SQL
;with CTE as
to prevent that error - especially if I am updating "shared" code or pasting a CTE out of context into a solution here.

If you were able to use a CTE then the solution is quite simple:
SQL
;WITH CTE AS
(
    SELECT [Child_ID], [Patent_ID], [Guid], [Level]
        , MtoE = CAST(isnull([Patent_ID],0) AS VARCHAR(MAX)) + '/' +
            CAST([Child_ID] AS VARCHAR(MAX))
    FROM Test0002
    WHERE [Level] = 1
    UNION ALL
    SELECT e.[Child_ID], e.[Patent_ID], e.[Guid], e.[Level]
        , MtoE = MtoE + '/' + CAST(e.[Child_ID] AS VARCHAR(MAX))
    FROM Test0002 e
    INNER JOIN CTE ecte ON ecte.[Child_ID] = e.[Patent_ID]
)
SELECT [Child_ID], [Patent_ID], [Guid], [Level]
FROM CTE EC
ORDER BY MtoE;
I give an explanation of how that works in my article Processing Loops in SQL Server[^]

Results
Child_ID	Patent_ID	Guid									Level
Child_001	Parent_001	268FF0B4-7328-40C2-94F1-8F963D54D00A	1
Child_002.1	Child_001	268FF0B4-7328-40C2-94F1-8F963D54D00A	2
Child_003.1	Child_002.1	268FF0B4-7328-40C2-94F1-8F963D54D00A	3
Child_003.2	Child_002.1	268FF0B4-7328-40C2-94F1-8F963D54D00A	3
Child_002.2	Child_001	268FF0B4-7328-40C2-94F1-8F963D54D00A	2
But let's assume that you can't use a CTE. At this post[^] Lukasz Szozda suggests something like this - which is only possible if you know the level depth before you start
SQL
SELECT p0.[Child_ID], p0.[Patent_ID], p0.[Guid], p0.[Level], 0 AS rLevel
FROM Test0002 p0
WHERE p0.[Level] = 1
UNION ALL
SELECT p1.[Child_ID], p1.[Patent_ID], p1.[Guid], p1.[Level], 1 AS rLevel
FROM Test0002 p1
JOIN (SELECT p0.[Child_ID], p0.[Patent_ID], p0.[Guid], p0.[Level], 0 AS rLevel
      FROM Test0002 p0
      WHERE p0.[Level] = 1) p0
  ON p1.[Patent_ID] = p0.[Child_ID]
UNION ALL
SELECT p2.[Child_ID], p2.[Patent_ID], p2.[Guid], p2.[Level], 2 AS rLevel
FROM Test0002 p2
JOIN (SELECT p1.[Child_ID], p1.[Patent_ID], p1.[Guid], p1.[Level], 1 AS rLevel
      FROM Test0002 p1
      JOIN (SELECT p0.[Child_ID], p0.[Patent_ID], p0.[Guid], p0.[Level], 0 AS rLevel
      FROM Test0002 p0
      WHERE p0.[Level] = 1) p0
        ON p1.[Patent_ID] = p0.[Child_ID]) p1
  ON p2.[Patent_ID] = p1.[Child_ID]
UNION ALL
SELECT p3.[Child_ID], p3.[Patent_ID], p3.[Guid], p3.[Level], 3 AS rLevel
FROM Test0002 p3
JOIN  (SELECT p2.[Child_ID], p2.[Patent_ID], p2.[Guid], p2.[Level], 2 AS rLevel
       FROM Test0002 p2
       JOIN (SELECT p1.[Child_ID], p1.[Patent_ID], p1.[Guid], p1.[Level], 1 AS rLevel
             FROM Test0002 p1
             JOIN (SELECT p0.[Child_ID], p0.[Patent_ID], p0.[Guid], p0.[Level], 0 AS rLevel
                    FROM Test0002 p0
                    WHERE p0.[Level] = 1) p0
              ON p1.[Patent_ID] = p0.[Child_ID]) p1
        ON p2.[Patent_ID] = p1.[Child_ID]) p2
  ON p3.[Patent_ID] = p2.[Child_ID];
As you can see, that gets difficult to follow - and it only caters for 3 levels of reporting.

So (still assuming you can't use CTEs) you will have to resort to a Loop (shudder). You need to "unpick" the recursive part of the CTE as many times as required.

The initial set-up (from the Anchor part of the CTE)
SQL
DECLARE @results TABLE (
	[Guid] [nvarchar](50) NOT NULL,
	[Level] [tinyint] NOT NULL,
	[Patent_ID] [nvarchar](50) NOT NULL,
	[Child_ID] [nvarchar](50) NOT NULL,
	[MtoE] [nvarchar](MAX) NOT NULL
) 

declare @maxLevel int = (SELECT MAX([Level]) FROM Test0002);

INSERT INTO @results 
SELECT [Guid], [Level],[Patent_ID],[Child_ID]
    , MtoE = CAST(isnull([Patent_ID],0) AS VARCHAR(MAX)) + '/' +
        CAST([Child_ID] AS VARCHAR(MAX))
FROM Test0002
WHERE [Level] = 1;
Then the recursive part becomes
SQL
DECLARE @currLevel int = 2; 

WHILE @currLevel <= @maxLevel
BEGIN
	INSERT INTO @results
	SELECT a.[Guid],a.[Level],a.[Patent_ID],a.[Child_ID]
		, MtoE = b.MtoE + '/' + CAST(a.[Child_ID] AS VARCHAR(MAX))
	FROM Test0002 a
	INNER JOIN @results b on a.[Patent_ID]=b.[Child_ID]
	WHERE a.[Level] = @currLevel
	SET @currLevel = @currLevel + 1
END;
SELECT [Child_ID], [Patent_ID], [Guid], [Level]
FROM @results
ORDER BY MtoE;
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900