Sunday, May 26, 2013
   
Text Size
Login

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:

  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

  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

  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

  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 

Comments   

Lucas
# 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
Add Comment

Name:

Email:

Website:

Message:



Human Check:

Security code
Refresh

Please type what you see in the image above:

Latest Posts

  • Joes Revolver Map (JRM)

    • Fri 17-May-13
      Hmmm... Sounds like a problem with the identifier. Was it working before and has there been a change ...
      Joel Lipman  
    • Fri 17-May-13
      Hello Joel: Yes, I do have it published on all pages of the site. I just went back to Revolver maps to ...
      Bill Duncan  
    • Fri 17-May-13
      Hi Bill, From the developers of RevolverMaps, "the module would need to be published on every page ...
      Joel Lipman  
    • Fri 17-May-13
      Hi Bill, I'll investigate further as you're not the first to say this happens. In the meantime, simply ...
      Joel Lipman  
    • Fri 17-May-13
      Its a great extension. But when I set it up I only show my presence on the 3D map and no other visitors ...
      Bill Duncan