Avatar of jdr0606
jdr0606Flag for United States of America

asked on 

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

SQLMicrosoft SQL Server

Avatar of undefined
Last Comment
Andrei Fomitchev
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

This was only posted in the SQL Topic Area.  That stands for Structured Query Language.

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
Simple fiddle showing it with your testcase:
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=502ac62a633ef4821170082966b97021

select OrderNumber, STRING_AGG(Flag, ',')
from orders
group by OrderNumber;

Open in new window

Avatar of jdr0606
jdr0606
Flag of United States of America image

ASKER

Sorry, I'm actually on SQL 2014

Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Andrei Fomitchev
Andrei Fomitchev
Flag of United States of America image

Blurred text
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.
See Pricing Options
Start Free Trial
Microsoft SQL Server
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
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo