Getting subquery error using string_agg function

Question

I'm using SQL Server Management Studio 2017 and I have a SELECT statement where I use STRING_AGG function in subquery like:

SELECT
    [p].[test],
    [p].[test], 
    ....
    (SELECT [Customers] 
     FROM 
         (SELECT
              STRING_AGG([C].[Name] , ', ') AS [Customers]
          FROM 
              [Project] AS [P]
          RIGHT JOIN 
              [ProjectCustomer] AS [PC] ON [P].[ProjectKey] = [PC].[ProjectKey]
          INNER JOIN 
              [Customer] AS [C] ON [PC].[CustomerKey] = [C].[CustomerKey]
          GROUP BY
              [P].[Name]) AS [t])
FROM 
    ...

Problem is when I executed it, I get this exception:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

But if I run subquery as a individual select it runs and it only returns one string value. Can someone explain me what am I doing wrong there? Regards

Answer 1

I ran into the same error message when using a GROUP BY and STRING_AGG in the same subquery. I added another subquery level to do the get mine to work. In your example, I would write this:

SELECT
  STRING_AGG(distinct_customers.[Name] , ', ') AS [Customers]
FROM
(
    SELECT
      [C].[Name]
    FROM 
      [Project] AS [P]
    RIGHT JOIN 
      [ProjectCustomer] AS [PC] ON [P].[ProjectKey] = [PC].[ProjectKey]
    INNER JOIN 
      [Customer] AS [C] ON [PC].[CustomerKey] = [C].[CustomerKey]
    GROUP BY
      [P].[Name]) AS [t]
) AS distinct_customers