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.

Solution 1

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

Solution 2

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

Solution 3

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.

Solution 4

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

View more solutions

Comments

[Devart]

+1. By the way GROUP BY not needed here.

[Amit Singh]

hmmm can you explain @Devart i mean the inner join give result like in image...so to combine in all duplicate m.maskid , m.maskname , m.schoolid , s.schoolname to one row we need group by

[Devart]

Relation between tblmask - maskdetails = 1 to many, so duplicates of records should not be here.

[Alessandro Bernardi]

STUFF is required for strip the first comma, in your case maskdetail ends with a comma

[pvgoran]

What's the reason for using PATH(''), TYPE and .value('.', 'NVARCHAR(MAX)') here, as opposed to simple PATH('') as in @AmitSingh's asnwer? Your variant yields a way, way heavier execution plan, does it have some hidden advantage to justify the cost? If not, would you correct or amend your answer since it's accepted and is supposed to be the best one?

[pvgoran]

Ok, I got it. Amit Singh's answer will return the string XML-encoded (because the result of for xml select is an XML text/object), so, for example, < will turn into >. Whereas your answer will return the string verbatim, because value() processes the XML object and extracts the text contents from there.

[Lee]

Fantastic works like a charm for my needs +1

[Magne]

See also: gooroo.io/GoorooTHINK/Article/10001/…

[NiallMitch14]

You're an sql wizard! Thanks so much