sql query group by sql server

Question

I have a sql server database table with columns as shown below :

Table1

Id Name ErrorId
1   AB     
2   CD    
3   AB    3
4   AB    4

I want to get an output something like this :

Name IdCount   ErrorIdCount  ErrorIds
AB      3           2           4,3 
CD      1           0            0

I wrote a query which looks like this currently :

select Name, Count(Id) as IdCount, 
Count(Distinct case when ErrorId != ' ' then Id END) as ErrorIdCount
from Table1 
group by Name;

It gives me something like this below :

Name IdCount ErrorIdCount.
AB      3        2
CD      1        0

I cannot figure out how I can include the ErrorIds too in my query ? Can anyone point me out how I can solve this ?

Answer 1

Declare @a table (Id int, Name varchar(10),ErrorId int)
insert into @a Values (1,'AB',null),(2,'CD',null),(3,'AB',3),(4,'AB',4);


Select Name, Count(Id) as IdCount, 
Count(Distinct case when ErrorId != ' ' then Id END) as ErrorIdCount
,[ErrorIds]=
STUFF((SELECT ', ' + Cast(ErrorId as Varchar(10))
    FROM @a iup           
    WHERE iup.Name = a.Name 
    order by  ErrorId  
    FOR XML PATH('')), 1, 1, '') 
from @a a
Group by Name