By Rahul


2013-07-11 10:54:00 8 Comments

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.

Thanks in advance.

4 comments

@AbdulRahman Ansari 2014-07-18 09:55:29

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 [email protected]

    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.

@Magne 2017-12-06 16:53:16

@roopaliv 2014-07-17 10:53:20

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

@Alessandro Bernardi 2016-08-26 09:41:49

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

@Devart 2013-07-11 10:56:03

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

@Amit Singh 2013-07-11 11:07:50

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 2013-07-11 11:10:27

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

@pvgoran 2016-09-05 13:24:16

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 2016-09-05 14:18:56

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 &gt;. Whereas your answer will return the string verbatim, because value() processes the XML object and extracts the text contents from there.

@Lee 2017-10-06 08:46:00

Fantastic works like a charm for my needs +1

@Amit Singh 2013-07-11 11:03:49

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

@Devart 2013-07-11 11:05:29

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

Related Questions

Sponsored Content

42 Answered Questions

[SOLVED] How to return only the Date from a SQL Server DateTime datatype

45 Answered Questions

25 Answered Questions

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

23 Answered Questions

[SOLVED] Check if table exists in SQL Server

11 Answered Questions

[SOLVED] SQL Server: How to Join to first row

16 Answered Questions

[SOLVED] LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

4 Answered Questions

[SOLVED] Inserting multiple rows in a single SQL query?

15 Answered Questions

[SOLVED] How to Delete using INNER JOIN with SQL Server?

37 Answered Questions

Sponsored Content