when try to make database separated comma i get error invalid in the select list because it is not contained in either an aggregate?

Question

I work on SQL Server 2012 I face error and I don't know how to solve error

Msg 8120, Level 16, State 1, Line 43
Column '#servers.ServerId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

What I need to do is display database separated comma for every application and server, because every application have one server and every server have multi database.

What I tried is :

SELECT
    p.ApplicationId,
    S.ServerName,
    [DataBase]= STUFF((SELECT DISTINCT ',' + pt.DatabaseName
                       FROM #Database pt
                       WHERE S.ServerId = pt.ServerId
                       FOR XML PATH(''), TYPE).value(N'./text()[1]', N'nvarchar(max)'), 1, 2, N'')
FROM 
    #application p
INNER JOIN 
    #servers S ON S.ServerId = P.ServerId
GROUP BY
    p.ApplicationId, S.ServerName
ORDER BY 
    p.ApplicationId, S.ServerName

Code table details

CREATE TABLE #application
(
    ApplicationId int,
    ServerId int
)

INSERT INTO #application (ApplicationId, ServerId)
VALUES (1, 1), (2, 2)

CREATE TABLE #servers
(
    ServerId int,
    ServerName nvarchar(50),
)

INSERT INTO #servers (ServerId, ServerName)
VALUES (1, 'LinuxServer'),
       (2, 'WindowsServer')

CREATE TABLE #Database
(
    DatabaseId int,
    ServerId int,
    DatabaseName nvarchar(50),
)

INSERT INTO #Database (DatabaseId, ServerId, DatabaseName)
VALUES (1, 1, 'DB1'), (2, 1, 'DB2'),
       (3, 2, 'DB3'), (4, 2, 'DB4')

Expected result

Answer 1

There is no need to use group by because for xml path('') would generate an aggregated string for each application and server.

Here is the code that will generate your expected result:

select
    ApplicationId,
    ServerName,
    stuff (Databases, 1, 2, N'')
from
(
    select
        a.ApplicationId,
        s.ServerName,
        (
            select ', ' + d.DatabaseName
            from #Database d
            where d.ServerId = s.ServerId
            order by d.DatabaseName
            for xml path('')
        ) Databases
    from #application a
    inner join #servers s on s.ServerId = a.ServerId
) q
order by ApplicationId, ServerName

suggestion1: Use stuff after implementing for xml path('').

suggestion2: Make sure to use order by in your for xml path('') query, so databases listed alphabetically.

note: There is not possible two identical database name in same server, so distinct is not necessary.