Support GROUP_CONCAT aggregation function - by phe

Status : 

  Duplicate<br /><br />
		This item appears to be a duplicate of another existing Connect or internal item.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


1
0
Sign in
to vote
ID 764820 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 9/26/2012 2:21:28 PM
Access Restriction Public

Description

MySQL has an aggregation function that concatenates strings with specified delimiter. The function is very useful in string handling. SQL Server lacks similar functionality. One work around is to use FOR XML PATH, but since FOR XML encoding special characters, the string sometimes is not the exact the concated value of the orginal string.

DECLARE @MyTable TABLE (id int not null, col1 nvarchar(30) NOT NULL, PRIMARY KEY CLUSTERED(id,col1));
INSERT @MyTable(id,col1) VALUES (1,'aaa'),(1,'bbb'),(2,'aaaa'),(2,'bbbb'),(2,'cccc'),(3,'ddddd'),(3,'fffff&');

SELECT t.id,
	STUFF(
		(
			SELECT ';'+x.col1
			FROM @MyTable x
			WHERE x.id=t.id
			ORDER BY x.col1
			FOR XML PATH('')
		),1,1,'') as con_str
FROM @MyTable t
GROUP BY t.id;

Output:
id	con_str
1	aaa;bbb
2	aaaa;bbbb;cccc
3	ddddd;fffff&amp;


If the GROUP_CONCAT is supported, the query can simply be:
SELECT id,GROUP_CONCAT(col1 ORDER BY col1 SEPARATOR ';') AS con_str FROM @MyTable GROUP BY id;
Sign in to post a comment.
Posted by Microsoft on 10/9/2012 at 11:21 AM
Hello,
I have resolved your request as duplicate of below:

http://connect.microsoft.com/SQLServer/feedback/details/427987/olap-function-for-string-concatenation

--
Umachandar, SQL Programmability Team