How to write a recursive SQL query on a self-referencing table

[Code4Ever]

I have a sample table you can see here. This self-referencing table is used for creating a tree structure in an Angular project. I want to write a SQL query to create a JSON result with a parent-child structure. Please help me.

[Richard Deeming]

You can't use FOR JSON to generate recursive JSON documents. You'll need to create a recursive user-defined function instead.

CREATE OR ALTER FUNCTION dbo.fn_OrganizationJson(@ParentId int)
RETURNS nvarchar(max)
As
BEGIN
DECLARE @json nvarchar(max);

If @ParentId Is Null
BEGIN
SET @json = (SELECT Id, Name, dbo.fn_OrganizationJson(Id) As Children FROM dbo.Organizations WHERE ParentId Is Null FOR JSON AUTO);
END
Else
BEGIN
SET @json = (SELECT Id, Name, dbo.fn_OrganizationJson(Id) As Children FROM dbo.Organizations WHERE ParentId = @ParentId FOR JSON AUTO);
END

Return @json;
END
GO

SQL Fiddle[^] NB: In some versions of SQL, you can't create a function that refers to itself if the function doesn't already exists. You may need to CREATE FUNCTION first with a dummy body, and then ALTER FUNCTION to add the implementation.

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer