September 10, 2010 at 12:24 pm
Does anyone have any idea about the T-SQL equivalent of group_concat function in MySQL?
Can you help me with MS SQL equivalent code for below MySQL statement.
select group_concat (Col1 order by field(Col2,8,13,15,53,55,6,73)) as FinalColumn from TestTable
Any help is greatly appriciated... thanks in advance...
September 10, 2010 at 12:28 pm
It sounds like you're looking for the Stuff() function.
May 9, 2011 at 9:36 pm
apat (9/10/2010)
select group_concat (Col1 order by field(Col2,8,13,15,53,55,6,73)) as FinalColumn from TestTable
Are you sure that's your SQL statement on MySQL? GROUP_CONCAT is an aggregate function so the SELECT statement that makes use of it usually contains a GROUP BY.
Here is an open-source project that emulates MySQL's GROUP_CONCAT functionality on SQL Server using SQLCLR User-Defined Aggregates.
http://groupconcat.codeplex.com/%5B/url%5D
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 9, 2011 at 10:42 pm
The T-SQL equivalent uses FOR XML PATH:
DECLARE @Data TABLE
(
group_id INTEGER NOT NULL,
data INTEGER NOT NULL
);
-- Sample data
INSERT @Data
(group_id,data)
VALUES
(1, 2),
(1, 8),
(1, 13),
(2, 15),
(2, 53),
(2, 55),
(3, 6),
(3, 73);
SELECT
G.group_id,
R.concatenated
FROM
(
-- Distinct groups
SELECT DISTINCT
group_id
FROM @Data
) AS G
CROSS APPLY
(
-- Concatenate in required order, returns XML
SELECT
',' + CONVERT(VARCHAR(12), D.data)
FROM @Data AS D
WHERE
D.group_id = G.group_id
ORDER BY
D.data ASC
FOR XML
PATH (''), TYPE
) AS GC (concatenated)
CROSS APPLY
(
-- Convert to VARCHAR from XML and remove leading delimiter
SELECT
STUFF(GC.concatenated.value('./text()[1]', 'VARCHAR(MAX)'), 1, 1, SPACE(0))
) AS R (concatenated)
Paul
March 14, 2012 at 12:44 pm
There are quite few ways to do so one is using cross tab queries other is using CLR based agreegate functions
kindly read MSDN Article for CLR based agreegate functions
http://msdn.microsoft.com/en-us/library/ms131056.aspx
or other articles of similar kind with little bit variation here and there
http://www.mssqltips.com/sqlservertip/2022/concat-aggregates-sql-server-clr-function/
http://dotnet-enthusiast.blogspot.in/2007/05/user-defined-aggregate-function-in-sql.html
For Cross Tab Based Solution
Sangram Desai.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy