Click here to Skip to main content
15,882,924 members
Home / Discussions / Database
   

Database

 
RantRe: Anyone here use Snowflake Pin
Eddy Vluggen1-Sep-22 3:49
professionalEddy Vluggen1-Sep-22 3:49 
GeneralRe: Anyone here use Snowflake Pin
Mike Ahrens1-Sep-22 10:34
professionalMike Ahrens1-Sep-22 10:34 
JokeRe: Anyone here use Snowflake Pin
Eddy Vluggen1-Sep-22 10:40
professionalEddy Vluggen1-Sep-22 10:40 
GeneralRe: Anyone here use Snowflake Pin
Mike Ahrens1-Sep-22 10:43
professionalMike Ahrens1-Sep-22 10:43 
QuestionRe: Anyone here use Snowflake Pin
Eddy Vluggen1-Sep-22 10:54
professionalEddy Vluggen1-Sep-22 10:54 
GeneralRe: Anyone here use Snowflake Pin
Eddy Vluggen1-Sep-22 10:55
professionalEddy Vluggen1-Sep-22 10:55 
QuestionHow to write a recursive SQL query on a self-referencing table Pin
Code4Ever29-Aug-22 8:13
Code4Ever29-Aug-22 8:13 
AnswerRe: How to write a recursive SQL query on a self-referencing table Pin
Richard Deeming29-Aug-22 21:35
mveRichard Deeming29-Aug-22 21: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 16:41
niksirat203024-Aug-22 16:41 
AnswerRe: How to write a program whose input various information of the companies Pin
Victor Nijegorodov24-Aug-22 20:19
Victor Nijegorodov24-Aug-22 20:19 
AnswerRe: How to write a program whose input various information of the companies Pin
Richard MacCutchan24-Aug-22 21:46
mveRichard MacCutchan24-Aug-22 21:46 
AnswerRe: How to write a program whose input various information of the companies Pin
Eddy Vluggen25-Aug-22 3:27
professionalEddy Vluggen25-Aug-22 3:27 
AnswerRe: How to write a program whose input various information of the companies Pin
Dave Kreskowiak25-Aug-22 3:45
mveDave Kreskowiak25-Aug-22 3:45 
Questionhow a data base file works Pin
Calin Negru20-Aug-22 21:08
Calin Negru20-Aug-22 21:08 
AnswerRe: how a data base file works Pin
Richard MacCutchan20-Aug-22 21:18
mveRichard MacCutchan20-Aug-22 21:18 
GeneralRe: how a data base file works Pin
Calin Negru21-Aug-22 9:59
Calin Negru21-Aug-22 9:59 
AnswerRe: how a data base file works Pin
Eddy Vluggen21-Aug-22 1:30
professionalEddy Vluggen21-Aug-22 1:30 
GeneralRe: how a data base file works Pin
Calin Negru21-Aug-22 2:53
Calin Negru21-Aug-22 2:53 
GeneralRe: how a data base file works Pin
Eddy Vluggen21-Aug-22 4:03
professionalEddy Vluggen21-Aug-22 4:03 
GeneralRe: how a data base file works Pin
Calin Negru21-Aug-22 5:47
Calin Negru21-Aug-22 5:47 
GeneralRe: how a data base file works Pin
Eddy Vluggen21-Aug-22 5:57
professionalEddy Vluggen21-Aug-22 5:57 
GeneralRe: how a data base file works Pin
Calin Negru21-Aug-22 9:20
Calin Negru21-Aug-22 9:20 
GeneralRe: how a data base file works Pin
Eddy Vluggen22-Aug-22 8:04
professionalEddy Vluggen22-Aug-22 8:04 
GeneralRe: how a data base file works Pin
Eddy Vluggen22-Aug-22 8:22
professionalEddy Vluggen22-Aug-22 8:22 
GeneralRe: how a data base file works Pin
RussellT13-Dec-22 12:29
professionalRussellT13-Dec-22 12: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.