7

I'm porting an application I originally wrote to run on Apache using PHP and a MySQL database. One of the queries used the MySQL functions of Concat_WS and Group_Concat to first concatenate a few different columns into one string, and then concatenate all items that were grouped together by the Group_By clause.

As an example:

ID    Name    Candy
1     John    M&Ms
1     John    KitKat

The query:

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

The 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've tried:

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

The problem can be seen in the fiddle that I have setup.

The expected result would be:

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

Lastly, this becomes even more difficult when I add more columns to the mix. I want to merge the results (as shown above) where the ID is the same. This works very well with group_concat because it will automatically merge rows that have been grouped together.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
What have you tried
  • 11,018
  • 4
  • 31
  • 45
  • 1
    @bluefeet I've seen that question already, but I am trying to avoid the use of XML. I really wanted to keep the same format that I had used in the previous version of the app. – What have you tried Jun 18 '13 at 20:19
  • There are ways to get the data in the format you want, but in SQL server there is no easy way to concatenate data from multiple rows into a single one. – Taryn Jun 18 '13 at 20:21
  • 1
    @bluefeet So how could I change this question so that it gets answered and not closed? I've done a massive amount of reading today on the subject and I still haven't gotten it myself (obviously as I'm here now) – What have you tried Jun 18 '13 at 20:24
  • @Whathaveyoutried You may want to explain why the duplicate does not answer your question, "the same format" is a bit vague, is there a problem with the query looking as it does or with the format of the result or...? – Joachim Isaksson Jun 18 '13 at 20:27
  • @JoachimIsaksson When I tried the use of `for xml path` on my own, I was given a result with xml titles in it, which I did not like. Something like `name_here` – What have you tried Jun 18 '13 at 20:31
  • @Whathaveyoutried `SELECT STUFF( (SELECT '-----' + Name + ';' + Candy FROM test FOR XML PATH('')) ,1,1,'')` does not give any XML tags, just the result. Seems @bluefeet beat me to an answer though. – Joachim Isaksson Jun 18 '13 at 20:33
  • @JoachimIsaksson You're right. It may have been something to do with Visual Studio, but after trying what you two have supplied me with, I'm up and running. Thank you very much. – What have you tried Jun 18 '13 at 20:43

1 Answers1

7

This can be done using a two-step process.

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

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

See Demo. This gives a result:

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

Once the initial concatenation is done, then 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 a result:

| ID |                     USERCANDY |
--------------------------------------
|  1 | -----John;MMs-----John;KitKat |
Taryn
  • 242,637
  • 56
  • 362
  • 405
  • I really appreciate this answer. I'm going to try to use it with my actual data set which is of course much more complicated than what I've posted. I will do my best to get it to work as expected but if I can't I will post here in a few. Thanks again. – What have you tried Jun 18 '13 at 20:32
  • This worked perfectly. I'm not sure what I was missing to be honest. I think the fact that the query was split up into two parts is what did the trick. I appreciate you not rushing to close this even though answers are out there, as I was having no luck with any of them. – What have you tried Jun 18 '13 at 20:42
  • @Whathaveyoutried I am glad you got it working. – Taryn Jun 18 '13 at 20:43
  • This worked out great for me! **Please note** => This doesn't handle escaping that XML encoding uses for special characters `<`, `>`, and `&`. See [this SO post on how to handle XML encoding](https://stackoverflow.com/a/10381975/175679) using `.value()` – SliverNinja - MSFT Sep 15 '17 at 09:01