question

TALENZ avatar image
TALENZ 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!!!
sql-server-2008t-sqlcursor
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Do a search for "xml path" and concatenation.
1 Like 1 ·
Here's an example. It seems like it would work. ACCT DATE REP Comment Seq Num 10000 20150318 JDE To whom it may 1 10000 20150318 JDE concern, I would 2 10000 20150318 JDE like to speak to 3 10000 20150318 JDE your momma now! 4
0 Likes 0 ·
iainrobertson avatar image
iainrobertson answered
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)
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pardon my ignorance, but row 11 with cte as ( select distinct . . . from #comments c )gives me a syntax error.
0 Likes 0 ·
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.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
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/
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.