How can I Select list of list or object of object not like a table? (SQL Server)-CodePudding

I have a problem in SQL Server: I want select list of list or object of object not a table.

Example :

SELECT p.Name, List (J.Name), .... 
FROM dbo.Person AS p
INNER JOIN dbo.Jobs AS j ON j.PersonId = p.id
GROUP BY p.name

Like a json example :

{
   Name : "test"
   Jobs : [
   {
       Name : "Job",
       Position : 2
   },{    
       Name : "Job1",
       Position : 1
   }]
}

Is there a solution to this problem for me?

I want to output a stored procedure that I can use for the web service

CodePudding user response：

Given this sample data:

CREATE TABLE dbo.Person(Id int, Name nvarchar(32));
INSERT dbo.Person VALUES(1, N'test');

CREATE TABLE dbo.Jobs(Id int, Name nvarchar(32), PersonId int, Position int);
INSERT dbo.Jobs VALUES(1, N'Job', 1, 2),(1, N'Job1',1,1);

You can just apply FOR JSON AUTO to your join to get the results you want (well, close):

SELECT p.Name, jobs.Name, jobs.Position
  FROM dbo.Person AS p
  INNER JOIN dbo.Jobs AS jobs 
    ON jobs.PersonId = p.id
  FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER;

Output (whitespace mine):

{
  "Name":"test",
  "jobs":[
  {
    "Name":"Job",
    "Position":2
  },{
    "Name":"Job1",
    "Position":1
  }]
}

Example db<>fiddle