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:

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
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
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
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

Add comment


Send

Comments   

0 Lucas Thursday, 21st June 2012, 6:42 pm
Hi Sir,

For the T-SQL, is there a way to do it with out the FOR XML PATH (''), I am trying to GROUP_CONCAT 2 cell [1111] and [2222] which all belong to ID column as
ID
1111
2222
My desired out put will be [1111,2222],rig ht now I am getting TAG_SEAL>111122 22, which have something to do with the FOR XML PATH (''). And if I get rid of it, it give me error "Subquery returned more than 1 value."

Your help is much appreciated !!!