MySQL Group_concat equivalent in T-SQL and Oracle

: Category: Databases; 28 November 2011; Hits: 27487

MySQL is just the best
Unfortunately I make a living using Microsoft and Oracle products. I shouldn't say unfortunately as I don't see myself doing any other job and it beats daytime television any day.

I use this quite a lot so I thought I'd put an article here somewhere. Based on the following concept:

copy raw

RowID     column_to_return_as_string   
--------- --------------------------
1         Me
2         Myself
3         I

-- to be returned as
RowID     my_field_name   
--------- --------------------------
1         Me,Myself,I

RowID column_to_return_as_string
--------- --------------------------
1 Me
2 Myself
3 I
-- to be returned as
RowID my_field_name
--------- --------------------------
1 Me,Myself,I

Note that the following queries include the nested version because I find myself needing to group concatenate more often from another table then using data from the same table.

MySQL

copy raw

SELECT
     (
	SELECT 
		GROUP_CONCAT(column_to_return_as_string) 
	FROM 
		a_pretend_table
	WHERE
		a_condition=true
     ) AS my_field_name
FROM
     another_pretend_table

SELECT
(
SELECT
GROUP_CONCAT(column_to_return_as_string)
FROM
a_pretend_table
WHERE
a_condition=true
) AS my_field_name
FROM
another_pretend_table

T-SQL

copy raw

SELECT
     STUFF(
       ( 
	 SELECT 
		',' + column_to_return_as_string 
	 FROM 
		a_pretend_table 
	 WHERE
		a_condition=true
	 FOR XML PATH ('')
       ),1,1,''
     ) AS my_field_name
FROM
     another_pretend_table

SELECT
STUFF(
(
SELECT
',' + column_to_return_as_string
FROM
a_pretend_table
WHERE
a_condition=true
FOR XML PATH ('')
),1,1,''
) AS my_field_name
FROM
another_pretend_table

Oracle

copy raw

SELECT
     (
	SELECT 
		wmsys.wm_concat(column_to_return_as_string) 
	FROM 
		a_pretend_table
	WHERE
		a_condition=true
     ) AS my_field_name
FROM
     another_pretend_table