Group concat with history in SQL Server

Question

This post is not intended to start a discussion about the best method of group concatenation of strings in SQL Server because many articles have already done that. I'd like to use a method like this for the group concatenation piece itself: https://groupconcat.codeplex.com/ but I'm open to other options. The issue is this: I need to see the history of the concats. Let's say I have data like this and can add more IDs as necessary:

╔═════════╦═════════════╦═══════════╦════════╦══════════════════════════════════════════════════════════════════════════════╗
║ chat_id ║ SpeakerName ║ SpeakerID ║ ConvID ║                                     Text                                     ║
╠═════════╬═════════════╬═══════════╬════════╬══════════════════════════════════════════════════════════════════════════════╣
║       1 ║ Ruby        ║         1 ║      1 ║ I need help                                                                  ║
║       2 ║ Ms. Kary    ║         2 ║      1 ║ Okay                                                                         ║
║       3 ║ Ruby        ║         1 ║      1 ║ i do not get this problem i am confusd                                       ║
║       4 ║ Ms. Kary    ║         2 ║      1 ║ In each of the possibie equations, see if it is the same as 9 ( x + 2) = 90. ║
║       5 ║ Beth        ║         4 ║      2 ║ where's ms Q                                                                 ║
║       6 ║ Ms J        ║         3 ║      2 ║ Not here today. will you please work with me ?                               ║
║       7 ║ Beth        ║         4 ║      2 ║ kk  thats fine                                                               ║
║       8 ║ Ms J        ║         3 ║      2 ║ what is 8 ÷ 10 written as a fraction                                         ║
╚═════════╩═════════════╩═══════════╩════════╩══════════════════════════════════════════════════════════════════════════════╝

Using the group concat functions would result in the data looking like this (note that I'm using " @@@ " as a delimiter to make it more easily readable):

╔════════╦══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╗
║ ConvID ║                                                                   ConcatenatedText                                                                   ║
╠════════╬══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╣
║      1 ║  In each of the possibie equations, see if it is the same as 9 ( x + 2) = 90.  @@@  i do not get this problem i am confusd @@@  Okay @@@ I need help ║
║      2 ║  what is 8 ÷ 10 written as a fraction @@@  kk  thats fine @@@  Not here today. will you please work with me ? @@@  where's ms Q                      ║
╚════════╩══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╝

What I need is this:

╔═════════╦═════════════╦═══════════╦════════╦══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╗
║ chat_id ║ SpeakerName ║ SpeakerID ║ ConvID ║                                                                   ConcatenatedText                                                                   ║
╠═════════╬═════════════╬═══════════╬════════╬══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╣
║       1 ║ Ruby        ║         1 ║      1 ║ I need help                                                                                                                                          ║
║       2 ║ Ms. Kary    ║         2 ║      1 ║ Okay @@@ I need help                                                                                                                                ║
║       3 ║ Ruby        ║         1 ║      1 ║ i do not get this problem i am confusd @@@  Okay @@@ I need help                                                                                    ║
║       4 ║ Ms. Kary    ║         2 ║      1 ║ In each of the possibie equations, see if it is the same as 9 ( x + 2) = 90.  @@@  i do not get this problem i am confusd @@@  Okay @@@ I need help ║
║       5 ║ Beth        ║         4 ║      2 ║ where's ms Q                                                                                                                                        ║
║       6 ║ Ms J        ║         3 ║      2 ║ Not here today. will you please work with me ? @@@  where's ms Q                                                                                    ║
║       7 ║ Beth        ║         4 ║      2 ║ kk  thats fine @@@  Not here today. will you please work with me ? @@@  where's ms Q                                                                ║
║       8 ║ Ms J        ║         3 ║      2 ║ what is 8 ÷ 10 written as a fraction @@@  kk  thats fine @@@  Not here today. will you please work with me ? @@@  where's ms Q                      ║
╚═════════╩═════════════╩═══════════╩════════╩══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╝

Notice that it is very similar to the first table but includes the discussion history.

Important data constraints:

The tables are structured so that every odd chat_id value is a person of type A and every even chat_id value is a person of type B. So, the conversation always appears in the data in sequential order of A,B,A,B,..., and so on.) Cases where the conversation would be A,A or B,B at any point due to variable lengths of conversational sequences have been eliminated. So, A, A will never occur, and B, B will never occur.
Every conversation has exactly 2 speakers. Cases where a different number of speakers existed for a given ConvID have been eliminated.
I'm running SQL Server 2016.

So, how do I get to my desired table/view representation from where I'm starting?

Probably easiest to use a recursive CTE to do the concatenation. Then the string gets built up a piece at a time naturally. — Martin Smith, Jul 28 '17 at 18:13

Kannan Kandasamy · Answer 1 · 2017-07-28T19:14:21.003

I think you can use stuff and xml path as below:

Select *,  
    stuff((select ' @@@ ' + [Text]  from #Speaker where s.convid = convid and chat_id <= s.chat_id order by chat_id desc for xml path('')),1,5,'') as ConcatenatedText
    from #Speaker s

Output as below:


+--------------------------------------------------------------------------------------------------------------------------------------------------+---------+-------------+-----------+--------+------------------------------------------------------------------------------+
|                                                                 ConcatenatedText                                                                 | chat_id | SpeakerName | SpeakerId | convid |                                     text                                     |
+--------------------------------------------------------------------------------------------------------------------------------------------------+---------+-------------+-----------+--------+------------------------------------------------------------------------------+
| I need help                                                                                                                                      |       1 | Ruby        |         1 |      1 | I need help                                                                  |
| Okay @@@ I need help                                                                                                                             |       2 | Ms. Kary    |         2 |      1 | Okay                                                                         |
| i do not get this problem i am confusd @@@ Okay @@@ I need help                                                                                  |       3 | Ruby        |         1 |      1 | i do not get this problem i am confusd                                       |
| In each of the possibie equations, see if it is the same as 9 ( x + 2) = 90. @@@ i do not get this problem i am confusd @@@ Okay @@@ I need help |       4 | Ms. Kary    |         2 |      1 | In each of the possibie equations, see if it is the same as 9 ( x + 2) = 90. |
| where's ms Q                                                                                                                                     |       5 | Beth        |         4 |      2 | where's ms Q                                                                 |
| Not here today. will you please work with me ? @@@ where's ms Q                                                                                  |       6 | Ms J        |         3 |      2 | Not here today. will you please work with me ?                               |
| kk  thats fine @@@ Not here today. will you please work with me ? @@@ where's ms Q                                                               |       7 | Beth        |         4 |      2 | kk  thats fine                                                               |
| what is 8 ÷ 10 written as a fraction @@@ kk  thats fine @@@ Not here today. will you please work with me ? @@@ where's ms Q                      |       8 | Ms J        |         3 |      2 | what is 8 ÷ 10 written as a fraction                                         |
+--------------------------------------------------------------------------------------------------------------------------------------------------+---------+-------------+-----------+--------+------------------------------------------------------------------------------+

Demo

I run into issues with special characters when using XML path based techniques. — devinbost, Jul 28 '17 at 19:10
http://rextester.com/BSUM20176 Can you check how you are getting error? — Kannan Kandasamy, Jul 28 '17 at 19:13
It's like this: https://stackoverflow.com/questions/26279715/for-xml-could-not-serialize-the-data-for-node-because-it-contains-a-character-0 — devinbost, Jul 28 '17 at 19:31
Also like this: https://stackoverflow.com/questions/25147627/xml-path-expression-to-include-special-characters/25164530#25164530 — devinbost, Jul 28 '17 at 19:31
You need to use `, TYPE` and `.value` to avoid those. As in the 2nd example here https://stackoverflow.com/a/451441/73226 — Martin Smith, Jul 28 '17 at 20:48

Group concat with history in SQL Server

1 Answers1