How to perform aggregation in T-sql like group_concat in MySQL?

Question

SQL

The ItemCodes table has fields:

• ItemCode - varchar
  
• ProjectId - varchar
  

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

Expected output: Code - number of projects - list of projects

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

I tried to compile this T-SQL query:

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

It turned out something like this query:

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

We'll have to sql xenm-a little "cheat"

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]

well, or play with a join to group

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 .