Click here to Skip to main content
15,564,500 members
Home / Discussions / Database
   

Database

 
RantRe: Anyone here use Snowflake Pin
Eddy Vluggen1-Sep-22 4:49
professionalEddy Vluggen1-Sep-22 4:49 
GeneralRe: Anyone here use Snowflake Pin
Mike Ahrens1-Sep-22 11:34
professionalMike Ahrens1-Sep-22 11:34 
JokeRe: Anyone here use Snowflake Pin
Eddy Vluggen1-Sep-22 11:40
professionalEddy Vluggen1-Sep-22 11:40 
GeneralRe: Anyone here use Snowflake Pin
Mike Ahrens1-Sep-22 11:43
professionalMike Ahrens1-Sep-22 11:43 
QuestionRe: Anyone here use Snowflake Pin
Eddy Vluggen1-Sep-22 11:54
professionalEddy Vluggen1-Sep-22 11:54 
GeneralRe: Anyone here use Snowflake Pin
Eddy Vluggen1-Sep-22 11:55
professionalEddy Vluggen1-Sep-22 11:55 
QuestionHow to write a recursive SQL query on a self-referencing table Pin
Code4Ever29-Aug-22 9:13
Code4Ever29-Aug-22 9:13 
AnswerRe: How to write a recursive SQL query on a self-referencing table Pin
Richard Deeming29-Aug-22 22:35
mveRichard Deeming29-Aug-22 22:35 
You can't use FOR JSON to generate recursive JSON documents. You'll need to create a recursive user-defined function instead.
SQL
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

QuestionHow to write a program whose input various information of the companies Pin
niksirat203024-Aug-22 17:41
niksirat203024-Aug-22 17:41 
AnswerRe: How to write a program whose input various information of the companies Pin
Victor Nijegorodov24-Aug-22 21:19
Victor Nijegorodov24-Aug-22 21:19 
AnswerRe: How to write a program whose input various information of the companies Pin
Richard MacCutchan24-Aug-22 22:46
mveRichard MacCutchan24-Aug-22 22:46 
AnswerRe: How to write a program whose input various information of the companies Pin
Eddy Vluggen25-Aug-22 4:27
professionalEddy Vluggen25-Aug-22 4:27 
AnswerRe: How to write a program whose input various information of the companies Pin
Dave Kreskowiak25-Aug-22 4:45
mveDave Kreskowiak25-Aug-22 4:45 
Questionhow a data base file works Pin
Calin Negru20-Aug-22 22:08
Calin Negru20-Aug-22 22:08 
AnswerRe: how a data base file works Pin
Richard MacCutchan20-Aug-22 22:18
mveRichard MacCutchan20-Aug-22 22:18 
GeneralRe: how a data base file works Pin
Calin Negru21-Aug-22 10:59
Calin Negru21-Aug-22 10:59 
AnswerRe: how a data base file works Pin
Eddy Vluggen21-Aug-22 2:30
professionalEddy Vluggen21-Aug-22 2:30 
GeneralRe: how a data base file works Pin
Calin Negru21-Aug-22 3:53
Calin Negru21-Aug-22 3:53 
GeneralRe: how a data base file works Pin
Eddy Vluggen21-Aug-22 5:03
professionalEddy Vluggen21-Aug-22 5:03 
GeneralRe: how a data base file works Pin
Calin Negru21-Aug-22 6:47
Calin Negru21-Aug-22 6:47 
GeneralRe: how a data base file works Pin
Eddy Vluggen21-Aug-22 6:57
professionalEddy Vluggen21-Aug-22 6:57 
GeneralRe: how a data base file works Pin
Calin Negru21-Aug-22 10:20
Calin Negru21-Aug-22 10:20 
GeneralRe: how a data base file works Pin
Eddy Vluggen22-Aug-22 9:04
professionalEddy Vluggen22-Aug-22 9:04 
GeneralRe: how a data base file works Pin
Eddy Vluggen22-Aug-22 9:22
professionalEddy Vluggen22-Aug-22 9:22 
GeneralRe: how a data base file works Pin
RussellT13-Dec-22 13:29
professionalRussellT13-Dec-22 13:29 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.