Как выполнить агрегацию в T-sql как group_concat в MySQL?

Question

[d'Ivan]

В таблице ItemCodes есть поля:

• ItemCode - varchar
  
• ProjectId - varchar
  

ItemCode ProjectId
A	P1
A	P2
A	P3
B	P2
C	P1
C	P3

Ожидаемый вывод: Код - кол-во проектов - перечень проектов

ItemCode '# of Projects' ProjectIdList
A	3	P1,P2,P3
B	1	P2
C	2	P1, P3

Пытался составить таким запросом T-SQL:

SELECT
    [ItemCode],
	  COUNT([ProjectId]) as ProjectsCnt,
	  STUFF((
			SELECT ','+[ProjectId] FROM [ItemCodes]
			WHERE [ProjectId]=C.[ProjectId]
			GROUP BY [ItemCode]
			FOR XML PATH('')
		) , 1 , 1 , '' ) AS ProjectIds
FROM [ItemCodes] C
GROUP BY [ItemCode]

Column '..ProjectId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Answer 1

[d'Ivan]

Получилось что-то наподобие такого запроса:

SELECT
    [ItemCode],
    COUNT([ProjectId]) as ProjectsCnt,
    ProjectIds AS ProjectId
FROM [ItemCodes] C
CROSS APPLY (
      SELECT ','+[ProjectId] FROM [ItemCodes]
      WHERE [ProjectId]=C.[ProjectId]
      GROUP BY [ItemCode],[ProjectId]
      FOR XML PATH('')
) D (ProjectIds)
GROUP BY [ItemCode]

Answer 2

[d-stream]

Придется sql xenm-чуть "обмануть"

SELECT
    [ItemCode],
    COUNT([ProjectId]) as ProjectsCnt,
min(
    STUFF((
      SELECT ','+[ProjectId] FROM [ItemCodes]
      WHERE [ProjectId]=C.[ProjectId]
      GROUP BY [ItemCode]
      FOR XML PATH('')
    ) , 1 , 1 , '' ) 
) AS ProjectIds
FROM [ItemCodes] C
GROUP BY [ItemCode]

ну или играть с джойном к группировке

Comments

[d'Ivan]

С использованием min

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

[d-stream]

Роман Мирр, ну тогда присоединять stuff к группировке:

select 
grp.ItemCode,
grp.ProjectsCnt,
stuff(…. из ItemCodes)
from (
SELECT
    [ItemCode],
    COUNT([ProjectId]) as ProjectsCnt
FROM [ItemCodes] C
GROUP BY [ItemCode]
) as grp 
inner join ItemCodes on ItemCodes.ItemCode=grp.ItemCode

Answer 3

[Константин Цветков]

SELECT ItemCode, 
  COUNT(ProjectId) AS [ProjectsCnt],
  (SELECT STRING_AGG(IC.ProjectId, ', ')
    FROM ItemCodes AS IC WHERE (IC.ItemCode = ItemCodes.ItemCode)) AS [ProjectIds]
FROM ItemCodes 
GROUP BY ItemCode

The Most Recent Updates for Microsoft SQL Server.