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
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.
Related Questions
Sponsored Content
11 Answered Questions
[SOLVED] SQL Server: How to Join to first row
- 2010-01-11 16:44:37
- Ian Boyd
- 449113 View
- 634 Score
- 11 Answer
- Tags: sql sql-server tsql sql-server-2000
44 Answered Questions
[SOLVED] How to concatenate text from multiple rows into a single text string in SQL server?
- 2008-10-11 23:49:59
- JohnnyM
- 1951655 View
- 1631 Score
- 44 Answer
- Tags: sql sql-server csv string-concatenation group-concat
16 Answered Questions
[SOLVED] LEFT JOIN vs. LEFT OUTER JOIN in SQL Server
- 2009-01-02 08:30:10
- KG Sosa
- 1324067 View
- 1361 Score
- 16 Answer
- Tags: sql-server tsql left-join outer-join
4 Answered Questions
[SOLVED] Inserting multiple rows in a single SQL query?
- 2009-01-17 05:55:54
- rits
- 2326678 View
- 1478 Score
- 4 Answer
- Tags: sql sql-server tsql insert
32 Answered Questions
[SOLVED] How do I UPDATE from a SELECT in SQL Server?
- 2010-02-25 14:36:53
- jamesmhaley
- 3673694 View
- 3237 Score
- 32 Answer
- Tags: sql sql-server tsql select
16 Answered Questions
[SOLVED] How to Delete using INNER JOIN with SQL Server?
- 2013-05-10 11:38:21
- nettoon493
- 1061031 View
- 998 Score
- 16 Answer
- Tags: sql sql-server sql-server-2008 inner-join sql-delete
38 Answered Questions
[SOLVED] How to return only the Date from a SQL Server DateTime datatype
- 2008-09-22 03:31:33
- eddiegroves
- 2425040 View
- 1543 Score
- 38 Answer
- Tags: sql sql-server tsql date datetime
24 Answered Questions
[SOLVED] How to check if a column exists in a SQL Server table?
- 2008-09-25 12:34:00
- Maciej
- 1055822 View
- 1671 Score
- 24 Answer
- Tags: sql-server sql-server-2008 tsql sql-server-2012 sql-server-2016
22 Answered Questions
[SOLVED] Check if table exists in SQL Server
- 2008-10-03 16:00:33
- Vincent
- 1029788 View
- 958 Score
- 22 Answer
- Tags: sql-server tsql sql-server-2005 sql-server-2000
37 Answered Questions
[SOLVED] Add a column with a default value to an existing table in SQL Server
- 2008-09-18 12:30:04
- Mathias
- 2514288 View
- 2437 Score
- 37 Answer
- Tags: sql sql-server sql-server-2005 sql-server-2000
4 comments
@AbdulRahman Ansari 2014-07-18 09:55:29
This can also be achieved using the
Scalar-Valued Function
inMSSQL 2008
Declare your function as following,
And then your final query will be like
Note: You may have to change the function, as I don't know the complete table structure.
@Magne 2017-12-06 16:53:16
See also: gooroo.io/GoorooTHINK/Article/10001/…
@roopaliv 2014-07-17 10:53:20
Please run the below query, it doesn't requires STUFF and GROUP BY in your case:
@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:
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 simplePATH('')
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>
. Whereas your answer will return the string verbatim, becausevalue()
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
@Devart 2013-07-11 11:05:29
+1. By the way
GROUP BY
not needed here.