0

I've got the below the SQL query that I'm using on SQL Server 2016. I'm trying to return in a specific format.

Issues:

  • The grouping isn't working, it is creating a new row for every Actor Id
  • The JSON is adding the ActorId as an object, and I just want the comma separated value. (When I remove the "AS ActorId" - I get this error "Column expressions and data sources without names or aliases cannot be formatted as JSON text using FOR JSON clause"

Query:

SELECT 
  Movie.Id AS 'Movie Id',
  Movie.Name AS 'Movie Name',
  (SELECT CAST(Actor.Id AS NVARCHAR(MAX)) AS 'ActorId' FOR JSON PATH) AS 'Actor Id Json List'
FROM Movie AS Movie
JOIN ActorLink AS ActorLink ON Movie.Id = ActorLink.MovieId
GROUP BY Movie.Id, Movie.Name, CAST(Actor.Id AS NVARCHAR(MAX))

enter image description here

dev-vsg
  • 97
  • 5
  • 3
    Does this answer your question? [SQL to JSON - array of objects to array of values in SQL 2016](https://stackoverflow.com/questions/37708638/sql-to-json-array-of-objects-to-array-of-values-in-sql-2016) If you were on 2017 I would recommend `STRING_AGG` – Charlieface Mar 09 '21 at 01:09
  • Yes. Thank you @Charlieface – dev-vsg Mar 09 '21 at 15:49

0 Answers0