135

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

enter image description here

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

enter image description here

And so on. So please help me while making a query for that.

Jan Schultke
  • 17,446
  • 6
  • 47
  • 96
Rahul
  • 5,603
  • 6
  • 34
  • 57
  • 19
    This isn't *quite* a duplicate of the question about SQL Server 2005 since the addition of [`STRING_AGG`](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql) to SQL Server 2017, so you might want to look into that if you're blessed with a recent SQL Server. – Matt Gibson Sep 27 '17 at 09:12

4 Answers4

189

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

Devart
  • 119,203
  • 23
  • 166
  • 186
  • 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 – Amit Singh Jul 11 '13 at 11:07
  • 5
    Relation between `tblmask` - `maskdetails` = `1 to many`, so duplicates of records should not be here. – Devart Jul 11 '13 at 11:10
  • 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 Sep 05 '16 at 13:24
  • 3
    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. – pvgoran Sep 05 '16 at 14:18
34
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
Amit Singh
  • 8,039
  • 20
  • 29
9

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.

AbdulRahman Ansari
  • 3,007
  • 1
  • 21
  • 29
  • See also: https://gooroo.io/GoorooTHINK/Article/10001/Aggregate-String-Concatenation-in-SQL-Server-2012-like-stringagg-in-PostgreSql/5122#.Wif5rLaZMWo – Magne Dec 06 '17 at 16:53
  • 1
    Works for me in SQL Server 2012. – knb Sep 05 '22 at 16:14
  • Never worked for anyone except by chance. It's undocumented and known to fail since the early 2000s when this first appeared. It's also a lot slower than any other method, up to 20 times compared to `FOR XML` – Panagiotis Kanavos Sep 01 '23 at 09:16
  • `NOLOCK` makes things a lot worse, as it can return *duplicate* rows – Panagiotis Kanavos Sep 01 '23 at 09:27
7

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
roopaliv
  • 449
  • 4
  • 7