SQL: Concatenate all fields matching a given key?

Question

Suppose I have a SQL query like this:

    SELECT
    tickets.TicketNumber, history.remarks
    FROM
    AT_DeviceReplacement_Tickets tickets
    INNER JOIN
    AT_DeviceReplacement_Tickets_History history
    ON tickets.TicketNumber = history.TicketNumber;

I get a table like this in repsonse:

ticketNumber | remarks
-------------+------------
           1 | "Hello, there is a problem."
           1 | "Did you check the power cable?
           1 | "We plugged it in and now it works.  Thank you!"
           2 | "Hello, this is a new ticket."

Suppose that I want to write a query that will concatenate the remarks for each ticket and return a table like this:

ticketNumber | remarks
-------------+------------
           1 | "Hello, there is a problem.Did you check the power cable?We plugged it in and now it works.  Thank you!"
           2 | "Hello, this is a new ticket."

Yes, in the real code, I've actually got these sorted by date, among other things, but just for the sake of discussion, how would I edit the above query to get the result I described?

I'm guessing that I need to use some sort of `GROUP BY` magic. — Vivian River, Apr 27 '11 at 18:13

score 3 · Answer 1 · edited May 23 '17 at 12:33

3

Have a look at the following questions:

Can I Comma Delimit Multiple Rows Into One Column?

Is it possible to concatenate column values into a string using CTE?

edited May 23 '17 at 12:33

Community

1
1

answered Apr 27 '11 at 18:16

Tim Lentine

7,782
5
35
40

score 1 · Answer 2 · answered Apr 27 '11 at 18:32

The cleanest solution to this problem is DB dependent. Lentine's links show very ugly solutions for Oracle and SQL Server and a clean one for MySQL. The answer in PostgreSQL is also very short and easy.

SELECT ticket_number, string_agg(remarks, ', ')
FROM 
AT_DeviceReplacement_Tickets tickets
INNER JOIN
AT_DeviceReplacement_Tickets_History history
ON tickets.Ticket_Number = history.Ticket_Number
GROUP BY tickets.ticket_number;

(Note you have both ticket_number and TicketNumber in your sample code.)

My guess is that Oracle and SQL Server either (1) have a similar aggregate function or (2) have the capability of defining your own aggregate functions. [For MySQL the equivalent aggregate is called GROUP_CONCAT.] What DB are you using?

SQL: Concatenate all fields matching a given key?

2 Answers2