asked
Is Cursor the Best Answer
I have a customer comments table that is organized in the following manner: Account Number, Date call taken, customer service rep, customer comments, time called, sequence number. The customer comments come from an ETL process where one customer call may have multiple lines. The comments field has a character length constraint of 40 characters. Comments from the same call that exceed 40 characters in length are given the same time, but a different sequence number. So for one call, I could have an account number, four or five rows of data that include account number, date, comments, time, and sequence number. I need to take each call and combine all of the comments in to one comment. I am not that familiar with what is the best way to concatenate the comments. I can isolate the conversation by time and sequence number. Then it's a matter of combining the comments into a single string. Thanks!!!
As @Mister-Magoo says, concatenating using for xml path is probably your best option. Here's an example using your sample data. The important point is to make surethat the criteria used inside the cross apply uniquely identifies each individual contact record. create table #comments (ACCT int, CallDate varchar(8), REP char(3), Comment varchar(20), SeqNum int) go insert #comments select 10000,'20150318','JDE','To whom it may ',1 union all select 10000,'20150318','JDE','concern, I would',2 union all select 10000,'20150318','JDE','like to speak to',3 union all select 10000,'20150318','JDE','your momma now! ',4 -- xml path ; with cte as ( select distinct ACCT , CallDate , REP from #comments c ) select cte.* , a.comment from cte cross apply ( select stuff( ( select ltrim(rtrim(Comment)) + ' ' -- this space is required to prevent the query returning actual xml as well as to separate values from #comments where ACCT = cte.ACCT and CallDate = cte.CallDate -- time mentioned in post but not given in example data should probably be added to this order by SeqNum for xml path('') ) , 1, 0, '') ) a (comment)
A common table expression has to be the first statement in a batch. I'd not added a semi-colon before the "with cte" bit, so this is probably the cause. I've edited the script to fix this.
Take a look at this article on [pivoting data][1]. That's what you need to do. You're turning rows into columns. [1]:
https://www.simple-talk.com/sql/t-sql-programming/crosstab-pivot-table-workbench/
17 People are following this question.
