Wednesday, May 16, 2012

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

Please note: all comments are reviewed before being published.


Security code
Refresh

Member Login

Joes Latest Members

A huge WELCOME goes to today's newest members:

  • infonat
Member Signups (Activated)
BeforeCurrentTrend
Day21=
Week105ê
Month11224ê
Year2,265612ê

Member Stats
51 guests are currently online.
1,178 members are still deciding.
There are 5,935 members in total.

Latest Comments

Paypal Donations

Want to support my work? Any donation is a blessing :c)