question

specterunseen avatar image
specterunseen asked

Eliminating duplicates while utilizing string concantenation with FOR XML

I'm having trouble with my attempt at grouping my concantenation effort. I still have duplicates. The dataset looks like: account_id reason 101 sampletext1 102 sampletext2 101 sampletext3 104 sampletext4 The data result should look like: account_id reason 101 sampletext1, sampletext3 (***GROUPED, DO NOT NEED THE SAME RESULT TWICE ****) 102 sampletext2 104 sampletext4 It seems the for XML does what I need with merging the two results, however I still get duplicate results. Can someone point me in the right direction of how to correctly group this to avoid duplicate results after the xml merges the data? SELECT ls.account_id ,ls.other_item ,ls.other_item2 ,stuff(( -- for xml concantenates all the strings select reason + ' | ' from #reasonsTempTable rs1 where rs1.loan_number = rs2.loan_number order by account_id for xml path('')),1,0,'') as reason_concantenated from #reasonTempTable2 rs2
t-sqlxml
10 |1200

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

1 Answer

·
Kevin Feasel avatar image
Kevin Feasel answered
Do a select distinct instead of just a select. The FOR XML PATH trick does the concatenation for each row, not for each distinct set of rows, so that is why you get "duplicate" records.
1 comment
10 |1200

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

perfect! i don't know why i didn't think of that :-)
0 Likes 0 ·

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.