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
Last Comment
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 OrderNumber
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
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