Concatenate row text to string have the sort

Question

Hi Experts,

I have below data

I want to concatenate the row text into a string.

The expected out put is as below. Please note there are duplicate description for the same category.

The end result should remove the duplicate and concatenate the rows to string as below.

I tried STRING_AGG function but could not get the sort/Order working

Appreciate your help here

Answer 1

Please don't give us your sample data as a picture. Instead, give us the CREATE TABLE or DECLARE TABLE and INSERT statements to give us the data (like, for example, I do below). Showing the desired output as a picture is fine.

Also, I was not sure whether you really wanted a leading ||, but only when there were multiple Description or whether that was a mistake and you didn't want the leading ||. So I did it both ways.

Declare @Sample Table(ID int, WOID int, Category char(1), Description varchar(20));
Insert @Sample(ID, WOID, Category, Description) Values
(1, 1234, 'A', 'Something 1'),
(2, 1234, 'B', 'Something 2'),
(3, 1234, 'C', 'Something 3'),
(4, 1234, 'D', 'Something 4'),
(5, 1234, 'A', 'Something 5'),
(6, 1234, 'A', 'Something 6'),
(7, 1234, 'A', 'Something 1'),
(8, 1234, 'A', 'Something 1'),
(9, 1234, 'A', 'Something 7'),
(10, 1234, 'A', 'Something 8');

-- If you don't want the leading ||
;With cte As
(Select Distinct WOID, Category, Description From @Sample)
Select WOID, Category, 
   String_Agg(Description, '||') WithIn Group (Order By Description)
From cte
Group By WOID, Category;

--If you do want the leading ||
;With cte As
(Select WOID, Category, Description, Count(*) As Nbr From @Sample Group By WOID, Category, Description)
Select WOID, Category, 
  String_Agg(Case When Nbr = 1 Then '' Else '||' End + Description, '||') WithIn Group (Order By Description)
From cte
Group By WOID, Category;

Tom

Answer 2

If you are using SQL Server version 2017 or above, then you can use STRING_AGG (Transact-SQL)