CODEWITHSUNDEEP
sqlsql-serveraggregate-functionsstring-concatenationstuff
B Leahy
B Leahy

Reputation: 13

Concatenating Multiple Rows Into String

I'm trying to concatenate multiple rows of a query into a string, but not getting the results I was expecting. The example that I'm working with should be returning one row, with the values of row 1 and row 2 concatenated in row 1 separated by commas. Instead, I'm getting two rows, with row 1 concatenated to itself and row 2 unchanged except for a comma in front.

Expected Result:

TYPEA, TYPE B

Actual Result:

,TYPEA,TYPEA
,TYPEB

SELECT DISTINCT t.ID,
                t.TicketNumber,
                tc.Description AS Code,
                (SELECT ',' + order_type 
                FROM Table_Order_Account oa
                WHERE oa.service_account = cs.CustomerServiceNumber
                FOR XML PATH ('')) AS Code3,
                oa.order_type AS Code2
FROM Table_Ticket t
WHERE (T.TicketType = 2 OR (t.tickettype = 1 AND tpc.Description = 'Records Update Needed'))

What am I doing wrong here? Thanks!

Note: I'm working in SQL Server 2014, so STRING_AGG isn't an option for me.

Upvotes: -1

Views: 744

Answers (1)

Charlieface
Charlieface

Reputation: 72395

The reason you are getting extra rows is probably related to the outer query missing a GROUP BY. It only uses DISTINCT, but it includes oa.order_type AS Code2, so it's not going to reduce the rows further.

Instead, remove that column and the LEFT JOIN Table_Order_Account as oa completely, and use something like GROUP BY t.ID, t.TicketNumber, tc.Description.

You also need to knock off the leading comma. You can use the STUFF function to do that.

The third parameter of STUFF should be the length of the separator.

You should also use .value to unescape any possible XML characters.

STUFF(
  (
    SELECT ',' + order_type 
    FROM Table_Order_Account oa
    WHERE oa.service_account = cs.CustomerServiceNumber
    FOR XML PATH (''), TYPE
  ).value('text()[1]', 'nvarchar(max)'),
  1, 1, '') AS Code3

And don't use NOLOCK, it has serious data integrity implications.

Upvotes: 1

Related Questions