Getting data from junction table as a list of values for unique records

Question

I have two tables connected with many-to-many relation by junction table:

    CREATE TABLE [dbo].[User](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](50) NOT NULL,
    [surname] [nvarchar](50) NOT NULL,
)

    CREATE TABLE [dbo].[Room](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [number] [nvarchar](50) NOT NULL
)

CREATE TABLE [dbo].[Permission](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [user_id] [int] NOT NULL,
    [room_id] [int] NOT NULL
)

I want to build an query that would give me in result name and surename from User table and list of rooms that given user have permission. For example:

name surname rooms
Jon  Doe     [134,143,132]

I managed to get a few results for Jon Doe each for one room but I'm not able to collapse this to get on record for each user.

Answer 1

In SQL Server prior 2017 this can be done with a corellated subquery FOR XML.

SELECT [User].[name],
       [User].[surname],
       stuff((SELECT ',' + [Room].[number] [text()]
                     FROM [dbo].[Room]
                          INNER JOIN [dbo].[Permission]
                                     ON [Permission].[room_id] = [Room].[ID]
                          WHERE [Permission].[user_id] = [User].[ID]
              FOR XML PATH('')), 1, 1, NULL) [rooms]
       FROM [dbo].[User];

Since 2017 there is is the string_agg() aggregation function with which strings of a group can be concatenated.

SELECT [User].[name],
       [User].[surname],
       string_agg([Room].[number], ',') [rooms]
       FROM [dbo].[User]
            INNER JOIN [dbo].[Permission]
                       ON [Permission].[user_id] = [User].[ID]
            INNER JOIN [dbo].[Room]
                       ON [Room].[ID] = [Permission].[room_id]
       GROUP BY [User].[ID],
                [User].[name],
                [User].[surname];

(Note: I did add [User].[ID] to the GROUP BY, as there might be more than one user with the same name.)