Menu
The SQL Server Equivalent to GROUP_CONCAT() This function allows you to return a result set as a comma-separated list, as opposed to listing each row as a separate row (as with a normal result set).
The STRING_AGG() is an aggregate function that concatenates rows of strings into a single string, separated by a specified separator. It does not add the separator at the end of the result string. In this syntax: input_string is any type that can be converted VARCHAR and NVARCHAR when concatenation.
GROUP_CONCAT is a function which concatenates/merges the data from multiple rows into one field. It is a GROUP BY function which returns a string if the group contains at least 1 non-null value, if it does not, it returns a Null value.
The difference here is while CONCAT is used to combine values across columns, GROUP_CONCAT gives you the capability to combine values across rows. It's also important to note that both GROUP_CONCAT and CONCAT can be combined to return desired results.
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
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
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With