SQL Server 2012 Merge Records in OUTER APPLY related table

Question

I have a Table "Customers" with customers details and Table "CALLS" where I store the result of each phonecall

When I need to get a list of the customers I have to call I use this query

SELECT *
FROM (
    SELECT TOP (50) S.ID,S.URL,S.Phone,S.Email
    FROM dbo.Customers AS S
    WHERE URL is not null and City like 'Berl%'
    ORDER BY S.ID 
    ) AS S

OUTER APPLY (
SELECT TOP (3) I.CalledOn, I.Answer
FROM dbo.Calls AS I
WHERE S.URL = I.URL
ORDER BY I.CalledOn DESC
) AS I;

where I get the list of all customer in the city together with last 3 answers.

But this returns up to 3 records for each customer, while I would have only one and summarize the value 3 values of CalledOn and Answer into the same record

to be more clear: Now:

+-----------+---------------+-------------+------------------+
|Customer 1 |  555-333 333  | 02-10-17    | Call Tomorrow    |
+-----------+---------------+-------------+------------------+
|Customer 2 |  555-444 333  | 02-10-17    | Call Tomorrow    |
+-----------+---------------+-------------+------------------+
|Customer 1 |  555-333 333  | 02-11-17    | Call Tomorrow    |
+-----------+---------------+-------------+------------------+
|Customer 1 |  555-333 333  | 02-12-17    | Stop Calling     |
+-----------+---------------+-------------+------------------+

Expected

+-----------+---------------+--------------------------------+
|Customer 1 |  555-333 333  | 02-12-17 : Call Stop Calling   |
|           |               | 02-11-17 : Call Tomorrow       |
|           |               | 02-10-17 : Call Tomorrow       |
+-----------+---------------+-------------+------------------+
|Customer 2 |  555-444 333  | 02-10-17    | Call Tomorrow    |
+-----------+---------------+-------------+------------------+

Currently I'm achieveing this with server-side logic, but I'm sure it can be done, easier and in a better way with TSQL

Can suggest the direction?

Thanks

McNets · Accepted Answer · 2017-02-16T09:00:31.483

For SQL-Server 2012

SELECT TOP (50) S.ID, S.URL, S.Phone, S.Email,
           STUFF((SELECT CHAR(10) + concat (I.CalledOn, ' ', I.Answer)
                  FROM dbo.Calls AS I
                  WHERE S.URL = I.URL
                  ORDER BY I.CalledOn DESC
                  FOR XML PATH('')
                ), 1, 1, '') AS CallAnswer
FROM dbo.Customers AS S
WHERE URL is not null and City like 'Berl%'
ORDER BY S.ID

vNext:

SELECT TOP (50) S.ID, S.URL, S.Phone, S.Email,
           (SELECT TOP (3) STRING_AGG(CONCAT(I.CalledOn, ' ', I.Answer), CHAR(13))
            FROM dbo.Calls AS I
            WHERE S.URL = I.URL
            ORDER BY I.CalledOn DESC
           ) AS CallAnswer
FROM dbo.Customers AS S
WHERE URL is not null and City like 'Berl%'
ORDER BY S.ID

Check it here: http://rextester.com/HSIEL20631

yessss!! unfortunately have to use 2012 version.. but it's OK Thanks a lot! — Joe, Feb 16 '17 at 17:06

SQL Server 2012 Merge Records in OUTER APPLY related table

1 Answers1