title: SQL - Combine 4 columns into one new column as a single JSON object
body: <p>Edit: FYI. So my PrimaryImage is actually an int and is used as a foreign key to my Images table. I just wanted to explain that so it is clear why I set it to <code>on f.PrimaryImage = i.Id</code>. For some reason all my rows are getting populated with every i.Id, i.EntityId, i.ImageTypeId, &amp; i.ImageUrl instead of just where my f.PrimaryImage = i.Id.</p>
  <p>I am writing a SQL stored procedure to SELECT ALL and I want to combine my last 4 columns <code>Id, EntityId, ImageTypeId, ImageUrl</code> into one new column <code>PrimaryImage</code> as a single JSON object. I was able to successfully do that with my Skills column but for that I needed it as an array that holds JSON objects so simply using <code>&quot;FOR JSON AUTO&quot;</code> took care of that. But like I said for <code>PrimaryImage</code>, I need that as a single JSON object that contains my <code>Id, EntityId, ImageTypeId, &amp; ImageUrl</code>. I included a picture of my result after executing this proc and right below my table I drew a representation of what I want the column to look like. Just to clarify I have four tables my Friends, FriendSkills, Skills, &amp; Images tables which I've used join statements organize accordingly. Basically my schema needs to look like this:</p>
  <pre><code>
  {
  &quot;id&quot;: 0000,
  &quot;userId&quot;: &quot;String&quot;
  &quot;bio&quot;: &quot;String&quot;,
  &quot;title&quot;: &quot;String&quot;,
  &quot;summary&quot;: &quot;String&quot;,
  &quot;headline&quot;: &quot;String&quot;,
  &quot;statusId&quot;: &quot;String&quot;,
  &quot;slug&quot;: &quot;String&quot;,
  &quot;skills&quot;: [{id: 0, name: &quot;String&quot;},{id: 0, name: &quot;String&quot;}],
  &quot;primaryImage&quot;: {
            &quot;id&quot;: 0,
            &quot;entityId&quot;: 0,
            &quot;imageTypeId&quot;: 0,
            &quot;imageUrl&quot;: &quot;String&quot;
          }
  }
  
  </code></pre>
  <p>Here is my stored procedure</p>
  <pre><code>
  ALTER PROC [dbo].[Friends_SelectAllV2]
  
  
  AS
  
  /* --- Test Proc ------
  
  Execute dbo.Friends_SelectAllV2
  
  */
  
  BEGIN
  
      Select  f.Id
              ,f.UserId
              ,f.DateAdded
              ,f.DateModified
              ,f.Title
              ,f.Bio
              ,f.Summary
              ,f.Headline
              ,f.Slug
              ,f.StatusId
              ,Skills = ( SELECT s.Id,
                          s.Name
                          From dbo.Skills as s inner join dbo.FriendSkills fs
                          on s.Id = fs.SkillId
                          Where  f.Id = fs.FriendId
                          FOR JSON AUTO
              ),
              PrimaryImage = (SELECT i.Id,
                              i.EntityId,
                              i.ImageTypeId,
                              i.ImageUrl
                              From dbo.Friends f left join dbo.Images as i
                              on f.PrimaryImage = i.Id
                              Where f.PrimaryImage = i.Id
                              FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
              )
              
      
  END
  
  
  </code></pre>