How to make a query with group_concat in sql server [duplicate]

I know that in sql server we cannot use Group_concat function but here is one issue i have in which i need to Group_Concat my query.I google it found some logic but not able to correct it.My sql query is

select m.maskid,m.maskname,m.schoolid,s.schoolname, md.maskdetail from tblmask m join school s on s.id = m.schoolid join maskdetails md on m.maskid = md.maskid order by m.maskname ; 

It gives me result like

Just look first 3 rows In that maskid,maskname,schoolid,schoolname is same but maskdetail is different so want to one row for that in which last column can contain all maskdetails as per maskid and so on.

I want my output like

And so on.So please help me while making a query for that.

Thanks in advance.

4 Answers

Query:

SELECT m.maskid , m.maskname , m.schoolid , s.schoolname , maskdetail = STUFF(( SELECT ',' + md.maskdetail FROM dbo.maskdetails md WHERE m.maskid = md.maskid FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') FROM dbo.tblmask m JOIN dbo.school s ON s.ID = m.schoolid ORDER BY m.maskname 

Additional information:

String Aggregation in the World of SQL Server

Select A.maskid , A.maskname , A.schoolid , B.schoolname , STUFF(( SELECT ',' + T.maskdetail FROM dbo.maskdetails T WHERE A.maskid = T.maskid FOR XML PATH('')), 1, 1, '') as maskdetail FROM dbo.tblmask A JOIN dbo.school B ON B.ID = A.schoolid Group by A.maskid , A.maskname , A.schoolid , B.schoolname 

This can also be achieved using the Scalar-Valued Function in MSSQL 2008
Declare your function as following,

CREATE FUNCTION [dbo].[FunctionName] (@MaskId INT) RETURNS Varchar(500) AS BEGIN DECLARE @SchoolName varchar(500) SELECT @SchoolName =ISNULL(@SchoolName ,'')+ MD.maskdetail +', ' FROM maskdetails MD WITH (NOLOCK) AND MD.MaskId=@MaskId RETURN @SchoolName END 

And then your final query will be like

SELECT m.maskid,m.maskname,m.schoolid,s.schoolname, (SELECT [dbo].[FunctionName](m.maskid)) 'maskdetail' FROM tblmask m JOIN school s on s.id = m.schoolid ORDER BY m.maskname ; 

Note: You may have to change the function, as I don't know the complete table structure.

Please run the below query, it doesn't requires STUFF and GROUP BY in your case:

Select A.maskid , A.maskname , A.schoolid , B.schoolname , CAST(( SELECT T.maskdetail+',' FROM dbo.maskdetails T WHERE A.maskid = T.maskid FOR XML PATH(''))as varchar(max)) as maskdetail FROM dbo.tblmask A JOIN dbo.school B ON B.ID = A.schoolid