How to make a query with group_concat in sql server [duplicate]
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 mJOIN dbo.school s ON s.ID = m.schoolidORDER BY m.maskname
Additional information:
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 AJOIN dbo.school B ON B.ID = A.schoolidGroup 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) ASBEGIN DECLARE @SchoolName varchar(500) SELECT @SchoolName =ISNULL(@SchoolName ,'')+ MD.maskdetail +', ' FROM maskdetails MD WITH (NOLOCK) AND MD.MaskId=@MaskId RETURN @SchoolNameEND
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.