error Column '#t1.ServerID' is invalid in the select list because it is not contained in either an aggregate

Question

when run sql statement below it give me error

SELECT
    p.ApplicationId,
    ServerName = STUFF((
        SELECT distinct ',' + pt.Server_Name
        FROM [dbo].[ServerNames] pt
        INNER JOIN #t1 t
            ON pt.ServerID = t.ServerID
       -- WHERE pt.Prod_IDM = p.Prod_ID
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, ''),
	[DataBase]=	STUFF((
        SELECT distinct ',' + pt.[DB_Name]
        FROM dbo.[DataBase] pt
        INNER JOIN [dbo].[ServerNames] t
            ON pt.ServerID = t.ServerID
       WHERE p.ServerID = t.ServerID
	   group by p.ServerID
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM #t1 p
group by p.ApplicationId
ORDER BY p.ApplicationId;

Msg 8120, Level 16, State 1, Line 24 Column '#t1.ServerID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. so How to solve this issue also another issue i need to solve i need to display comma separated after first item because on statement above it display before first item

Answer 1

Hi @Ahmed Salah Abed Elaziz If you are able to use STRING_AGG, then check this query:

;WITH CTE1 AS
(
 SELECT ApplicationId,STRING_AGG(Server_Name,',') AS Server_Name
 FROM #t1 t INNER JOIN [dbo].[ServerNames] s ON s.ServerID = t.ServerID
 GROUP BY ApplicationId
),CTE2 AS
(
 SELECT ApplicationId,STRING_AGG([DB_Name],',') AS [DB_Name]
 FROM #t1 t INNER JOIN [dbo].[DataBase] db ON db.ServerID = t.ServerID
 GROUP BY ApplicationId
)
SELECT C1.ApplicationId,C1.Server_Name,C2.DB_Name
FROM CTE1 C1 JOIN CTE2 C2 ON C1.ApplicationId=C2.ApplicationId

Best regards,

Cosmog Hong

Answer 2

Why do you use GROUP BY in the first place? Remove both instances of it. Instead change

FROM #t1 p

with

FROM (SELECT DISTINCT ApplicationId FROM #t1) p

Or, if you are on SQL 2017 or later, rewrite the query to use string_agg instead. In which case you will need to use GROUP BY.

Answer 3

Hi @Ahmed Salah Abed Elaziz Modify your initial code by removing three red lines as shown in below pic.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.