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

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:

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 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.