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
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.
