T SQL Group column with accumulating data in one record

Question

I am trying to write a query on SQL Server to merge data items in 1 record

Input table:

sales_ref_no    Description
001 Hello
001 Hi
002 Dear
002 All
002 Please
003 Thanks

Output table:

sales_ref_no    Description
001 Hello | Hi
002 Dear | All | Please
003 Thanks

The description under the same sales_ref_no is accumulated under the same record using a | delimiter

Can anyone help?

Thanks!

try to google sql server string aggregation. There're a lot of answer on stackoverflow.com — Roman Pekar, Mar 15 '15 at 07:00

score 0 · Accepted Answer · answered Mar 15 '15 at 07:05

Select 
  sales_ref_no,
  STUFF((
  SELECT ' | ' + B.Description
   FROM YOUR_TABLE B
   WHERE (B.sales_ref_no = A.sales_ref_no) 
   FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
  ,1,2,'') AS Description
From YOUR_TABLE A
Group By sales_ref_no

T SQL Group column with accumulating data in one record

1 Answers1