How to make a query with group concat in sql server

Question

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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql-server

Examples related to string-aggregation

Examples related to sql-server-group-concat