How can I put SELECT result set to SELECT column?-CodePudding

I have two tables: Job(ID,Name, etc.) and Address(ID, Job_ID, Name etc). I want to get result like this:

[
  {
    "Job_ID": 1,
    "JobName": "Test",
    "Addresses": [
     {
       "ID": 1,
       "Name": "King street"
     },
     {
       "ID": 2,
       "Name": "Queen`s street
     }
     ]
  }
]

My current query that gets only one address for a job looks like this:

    SELECT TOP 100 
    JO.ID,
    JO.Closed as Deleted,
    JO.Number as JobNumber,
    JO.Name as JobName,
    Convert(date, JO.Start_Date) as Start_Date,
    JO.Job_Status_ID as Status,
    A.ID as Address_ID,
    A.Name as Name,
    A.Number as Number,
    A.Sort_Name as Sort_Name,
    A.Address_1 as Address_1,
    A.Address_2 as Address_2,
    A.ZipCode as ZIP,
    A.E_Mail_Address as Email,
    A.Web_Site_URL as Web_Site_URL,
    A.TAXRATE as Tax_Rate,
    A.State
FROM Job JO
             INNER JOIN Address A ON A.Job_Id = JO.ID

Is it possible without pivot table(Address_ID, Job_ID)?

CodePudding user response：

You can use FOR JSON to convert you results to JSON. This gives the result you are looking for:

CREATE TABLE #Job (ID INT NOT NULL, Name VARCHAR(50));
INSERT #Job (ID, Name)
VALUES (1, 'Job 1'), (2, 'Job 2');

CREATE TABLE #Address (ID INT NOT NULL, JobID INT NOT NULL, Name VARCHAR(50));
INSERT #Address (ID, JobID, Name) 
VALUES (1, 1, 'King street'), (2, 1, 'Queen''s street'), (3, 2, 'Address 3'), (4, 2, 'Address 4');

SELECT  JobID = j.ID, 
        JobName = j.Name,
        Addresses = (   SELECT  a.ID, a.Name
                        FROM    #Address AS a
                        WHERE   a.JobID = j.ID
                        FOR JSON AUTO
                    ) 
FROM    #Job AS j
FOR JSON AUTO;