How to make a query with group_concat in sql server.

Question

I know that in the 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 googled 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 at the 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.

Answer 1

That could also be performed using the Scalar-Valued Function in MSSQL 2008
Define your function as follows,

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 ; 

If you want to learn more about SQL, Check out this SQL Certification by Intellipaat.