ItemCode ProjectId
A P1
A P2
A P3
B P2
C P1
C P3ItemCode '# of Projects' ProjectIdList
A 3 P1,P2,P3
B 1 P2
C 2 P1, P3SELECT
[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.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]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]
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
Простой
