Group concat with history in SQL Server -
this post not intended start discussion best method of group concatenation of strings in sql server because many articles have done that. i'd use method group concatenation piece itself: https://groupconcat.codeplex.com/ i'm open other options. issue this: need see history of concats. let's have data , can add more ids necessary:
╔═════════╦═════════════╦═══════════╦════════╦══════════════════════════════════════════════════════════════════════════════╗ ║ chat_id ║ speakername ║ speakerid ║ convid ║ text ║ ╠═════════╬═════════════╬═══════════╬════════╬══════════════════════════════════════════════════════════════════════════════╣ ║ 1 ║ ruby ║ 1 ║ 1 ║ need ║ ║ 2 ║ ms. kary ║ 2 ║ 1 ║ okay ║ ║ 3 ║ ruby ║ 1 ║ 1 ║ not problem confusd ║ ║ 4 ║ ms. kary ║ 2 ║ 1 ║ in each of possibie equations, see if same 9 ( x + 2) = 90. ║ ║ 5 ║ beth ║ 4 ║ 2 ║ where's ms q ║ ║ 6 ║ ms j ║ 3 ║ 2 ║ not here today. please work me ? ║ ║ 7 ║ beth ║ 4 ║ 2 ║ kk thats fine ║ ║ 8 ║ ms j ║ 3 ║ 2 ║ 8 ÷ 10 written fraction ║ ╚═════════╩═════════════╩═══════════╩════════╩══════════════════════════════════════════════════════════════════════════════╝ using group concat functions result in data looking (note i'm using " @@@ " delimiter make more readable):
╔════════╦══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╗ ║ convid ║ concatenatedtext ║ ╠════════╬══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╣ ║ 1 ║ in each of possibie equations, see if same 9 ( x + 2) = 90. @@@ not problem confusd @@@ okay @@@ need ║ ║ 2 ║ 8 ÷ 10 written fraction @@@ kk thats fine @@@ not here today. please work me ? @@@ where's ms q ║ ╚════════╩══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╝ what need this:
╔═════════╦═════════════╦═══════════╦════════╦══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╗ ║ chat_id ║ speakername ║ speakerid ║ convid ║ concatenatedtext ║ ╠═════════╬═════════════╬═══════════╬════════╬══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╣ ║ 1 ║ ruby ║ 1 ║ 1 ║ need ║ ║ 2 ║ ms. kary ║ 2 ║ 1 ║ okay @@@ need ║ ║ 3 ║ ruby ║ 1 ║ 1 ║ not problem confusd @@@ okay @@@ need ║ ║ 4 ║ ms. kary ║ 2 ║ 1 ║ in each of possibie equations, see if same 9 ( x + 2) = 90. @@@ not problem confusd @@@ okay @@@ need ║ ║ 5 ║ beth ║ 4 ║ 2 ║ where's ms q ║ ║ 6 ║ ms j ║ 3 ║ 2 ║ not here today. please work me ? @@@ where's ms q ║ ║ 7 ║ beth ║ 4 ║ 2 ║ kk thats fine @@@ not here today. please work me ? @@@ where's ms q ║ ║ 8 ║ ms j ║ 3 ║ 2 ║ 8 ÷ 10 written fraction @@@ kk thats fine @@@ not here today. please work me ? @@@ where's ms q ║ ╚═════════╩═════════════╩═══════════╩════════╩══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╝ notice similar first table includes discussion history.
important data constraints:
- the tables structured every odd chat_id value person of type a , every chat_id value person of type b. so, conversation appears in data in sequential order of a,b,a,b,..., , on.) cases conversation a,a or b,b @ point due variable lengths of conversational sequences have been eliminated. so, a, never occur, , b, b never occur.
- every conversation has 2 speakers. cases different number of speakers existed given convid have been eliminated.
- i'm running sql server 2016.
so, how desired table/view representation i'm starting?
i think can use stuff , xml path below:
select *, stuff((select ' @@@ ' + [text] #speaker s.convid = convid , chat_id <= s.chat_id order chat_id desc xml path('')),1,5,'') concatenatedtext #speaker s output below:
+--------------------------------------------------------------------------------------------------------------------------------------------------+---------+-------------+-----------+--------+------------------------------------------------------------------------------+ | concatenatedtext | chat_id | speakername | speakerid | convid | text | +--------------------------------------------------------------------------------------------------------------------------------------------------+---------+-------------+-----------+--------+------------------------------------------------------------------------------+ | need | 1 | ruby | 1 | 1 | need | | okay @@@ need | 2 | ms. kary | 2 | 1 | okay | | not problem confusd @@@ okay @@@ need | 3 | ruby | 1 | 1 | not problem confusd | | in each of possibie equations, see if same 9 ( x + 2) = 90. @@@ not problem confusd @@@ okay @@@ need | 4 | ms. kary | 2 | 1 | in each of possibie equations, see if same 9 ( x + 2) = 90. | | where's ms q | 5 | beth | 4 | 2 | where's ms q | | not here today. please work me ? @@@ where's ms q | 6 | ms j | 3 | 2 | not here today. please work me ? | | kk thats fine @@@ not here today. please work me ? @@@ where's ms q | 7 | beth | 4 | 2 | kk thats fine | | 8 ÷ 10 written fraction @@@ kk thats fine @@@ not here today. please work me ? @@@ where's ms q | 8 | ms j | 3 | 2 | 8 ÷ 10 written fraction | +--------------------------------------------------------------------------------------------------------------------------------------------------+---------+-------------+-----------+--------+------------------------------------------------------------------------------+ 
Comments
Post a Comment