Print

MySQL Group_concat equivalent in T-SQL and Oracle

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:
copyraw 
RowID     column_to_return_as_string   
--------- --------------------------
1         Me
2         Myself
3         I

-- to be returned as
RowID     my_field_name   
--------- --------------------------
1         Me,Myself,I
  1.  RowID     column_to_return_as_string 
  2.  --------- -------------------------- 
  3.  1         Me 
  4.  2         Myself 
  5.  3         I 
  6.   
  7.  -- to be returned as 
  8.  RowID     my_field_name 
  9.  --------- -------------------------- 
  10.  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
copyraw 
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
  1.  SELECT 
  2.       ( 
  3.      SELECT 
  4.          GROUP_CONCAT(column_to_return_as_string) 
  5.      FROM 
  6.          a_pretend_table 
  7.      WHERE 
  8.          a_condition=true 
  9.       ) AS my_field_name 
  10.  FROM 
  11.       another_pretend_table 

T-SQL
copyraw 
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
  1.  SELECT 
  2.       STUFF( 
  3.         ( 
  4.       SELECT 
  5.          ',' + column_to_return_as_string 
  6.       FROM 
  7.          a_pretend_table 
  8.       WHERE 
  9.          a_condition=true 
  10.       FOR XML PATH ('') 
  11.         ),1,1,'' 
  12.       ) AS my_field_name 
  13.  FROM 
  14.       another_pretend_table 

Oracle
copyraw 
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
  1.  SELECT 
  2.       ( 
  3.      SELECT 
  4.          wmsys.wm_concat(column_to_return_as_string) 
  5.      FROM 
  6.          a_pretend_table 
  7.      WHERE 
  8.          a_condition=true 
  9.       ) AS my_field_name 
  10.  FROM 
  11.       another_pretend_table 
Category: Databases :: Article: 403