Running MYSQL Group_Concat in SQL Server

Question

I am porting an application that I originally wrote to run on Apache using PHP and a MySQL database. One of the queries used the MySQL Concat_WS and Group_Concat to first combine several different columns into a single row and then combine all the elements grouped together with the Group_By .

As an example:

 ID Name Candy 1 John M&Ms 1 John KitKat 

Request:

 Select Group_Concat(Concat_WS('-',Name, Candy) Separator '00--00') as UserCandy From ExampleTable Group By ID 

Result:

 UserCandy John-M&Ms00--00John-KitKat 

Now I am trying to accomplish the same result in SQL Server 2008 using PHP 5.4 +.

What I tried:

 SELECT Stuff(name + ';' + candy, 1, 0, '-----') AS UserCandy FROM test 

The problem can be seen in the script that I installed .

Expected Result:

 -----John;MMs-----John;KitKat 

Finally, it gets even more complicated when I add more columns to the mix. I want to combine the results (as shown above), where the identifier is the same. This works very well with Group_Concat because it automatically concatenates the lines that were grouped together.

Answer 1

This can be done using a two-step process.

First, you can use Common Table Expression to perform the first concatenation of name and candy . The first part uses the query:

 ;with cte as ( select id, name+';'+ candy UserCandy from table_test ) select * from cte 

See Demo . This gives the result:

 | ID | USERCANDY | -------------------- | 1 | John;MMs | | 1 | John;KitKat | 

Once the initial concatenation is done, you can use FOR XML PATH and STUFF to get the final result:

 ;with cte as ( select id, name+';'+ candy UserCandy from table_test ) select distinct c.id, STUFF( (SELECT '-----' + c2.UserCandy FROM cte c2 where c.id = c2.id FOR XML PATH ('')) , 1, 0, '') AS UserCandy from cte c; 

See SQL Fiddle with Demo . This gives the result:

 | ID | USERCANDY | -------------------------------------- | 1 | -----John;MMs-----John;KitKat |