CODEWITHSUNDEEP
sqlssms
Ivancito
Ivancito

Reputation: 169

Concatening in a specific order given by a number

I have a table like this:

enter image description here

I want to concatenate the Product name in the given Product_order by ID.

Therefore, I should get something like: CC-TC-CA for ID 1.

Upvotes: 1

Views: 52

Answers (2)

Steve Wakeford
Steve Wakeford

Reputation: 331

If you can use a stored procedure instead of a single query the += operator can do what you're looking for.

DECLARE @Product_order VARCHAR(100) = '';
SELECT @Product_order += Product + '-' FROM [table] WHERE id = 1 ORDER BY Product_Order;
SELECT SUBSTRING(@Product_order, 0, LEN(@Product_order));

Update: I've learned that returning multiple rows and using in an assignment in the select clause is unsupported behavior in SQL Server.

Upvotes: 0

Fahmi
Fahmi

Reputation: 37483

you can use string_agg()- it'll work sql server 2016+

select id, string_Agg(product,',') as pname
from tablename
group by id

OR you can use stuff()

SELECT id,
       STUFF((SELECT ',' + product
              FROM tablename AS T1
              WHERE  T1.id = T2.id
              FOR XML PATH('')), 1, 1, '')
FROM   tablename AS T2
GROUP  BY id

Upvotes: 1

Related Questions