Concatenate similar SQL transaction rows into single
I have a table of order #'s and hold flags for those orders. I'd like to group the orders into a table/view with a single row and a field containing the flags for the order.
Example
Transaction list of orders
OrderNumber Flag
Order1 WC
Order1 STOP
Order1 OK
Order2 WC
Order2 OK
Group Result
OrderNumber Flags
Order1 WC,STOP,OK
Order2 WC,OK
How is the best way to do something like this?
Thanks
Example
Transaction list of orders
OrderNumber Flag
Order1 WC
Order1 STOP
Order1 OK
Order2 WC
Order2 OK
Group Result
OrderNumber Flags
Order1 WC,STOP,OK
Order2 WC,OK
How is the best way to do something like this?
Thanks
Simple fiddle showing it with your testcase:
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=502ac62a633ef4821170082966b97021
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=502ac62a633ef4821170082966b97021
select OrderNumber, STRING_AGG(Flag, ',')
from orders
group by OrderNumber;ASKER
Sorry, I'm actually on SQL 2014
try if this worked in SQL 2014?
;with txn as
(
select 'Order1' OrderNumber, 'WC' Flag union all
select 'Order1' OrderNumber, 'STOP' Flag union all
select 'Order1' OrderNumber, 'OK' Flag union all
select 'Order2' OrderNumber, 'WC' Flag union all
select 'Order2' OrderNumber, 'OK' Flag
)
SELECT OrderNumber, STUFF((
SELECT ',' + Flag
FROM txn
Where OrderNumber = a.OrderNumber
FOR XML PATH('')
), 1, 1, '') Flags
FROM txn a
Group by OrderNumberASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It doesn't say what database product you are using.
Based on your previous questions, I'm guessing SQL Server.
Now, what version are you using?
If 2017 or later, it's a simple string_agg:
https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15