MySQL Group_concat equivalent in T-SQL and Oracle @ Joel Lipman .Com

Latest:

MySQL Group_concat equivalent in T-SQL and Oracle

Last Updated on Monday, 19 November 2012

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

Comments

# Lucas Thu, 21st June 2012

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

Like | Dislike | 0 Reply | Reply with quote | Quote

Refresh comments list
RSS feed for comments to this post

Add Comment