Query to get distinct results

Question

I have a query to get data from multiple tables. But somehow, I got duplicate entries from database due to one column.

The query is:

SELECT
  BH.BusinessName, AppointmentStartTime, AppointmentEndTime, AppointmentStatus,
  AppointmentFor, AppointmentForID, AppointmentStatus, S.ServiceName
from Appointment A
INNER JOIN BusinessHost BH ON A.BusinessHostID = BH.BusinessHostID
INNER JOIN BusinessHostService BHS ON BHS.BusinessHostID=BH.BusinessHostID
INNER JOIN Services S ON S.ServiceID=BHS.ServiceID

and output is like this:

+ -------------+----------------------+--------------------+-------------------+----------------+------------------+-------------------+---------------------+
| BusinessName | AppointmentStartTime | AppointmentEndTime | AppointmentStatus | AppointmentFor | AppointmentForID | AppointmentStatus | ServiceName         |
+ -------------+----------------------+--------------------+-------------------+----------------+------------------+-------------------+---------------------+
| Amit & Sons  | 02:00:00.0000000     | 02:15:00.0000000   | Added             | Motor-Cycle    | 1006             | Added             | Arboriculture       |
| Amit & Sons  | 02:00:00.0000000     | 02:15:00.0000000   | Added             | Motor-Cycle    | 1006             | Added             | Landscaping Service |
| Rohit & Sons | 02:30:00.0000000     | 02:45:00.0000000   | Added             | Motor-Cycle    | 1006             | Added             | Arboriculture       |
| Rohit & Sons | 02:30:00.0000000     | 02:45:00.0000000   | Added             | Motor-Cycle    | 1006             | Added             | Landscaping Service |
+ -------------+----------------------+--------------------+-------------------+----------------+------------------+-------------------+---------------------+

can you help me with writing the query to get desired results like:

+ -------------+----------------------+--------------------+-------------------+----------------+------------------+-------------------+------------------------------------+
| BusinessName | AppointmentStartTime | AppointmentEndTime | AppointmentStatus | AppointmentFor | AppointmentForID | AppointmentStatus | ServiceNames                       |
+ -------------+----------------------+--------------------+-------------------+----------------+------------------+-------------------+------------------------------------+
| Amit & Sons  | 02:30:00.0000000     | 02:45:00.0000000   | Added             | Motor-Cycle    | 1006             | Added             | Arboriculture, Landscaping Service |
| Rohit & Sons | 02:30:00.0000000     | 02:45:00.0000000   | Added             | Motor-Cycle    | 1006             | Added             | Arboriculture, Landscaping Service |
+ -------------+----------------------+--------------------+-------------------+----------------+------------------+-------------------+------------------------------------+

You are looking for an aggregation with a string concatenation. This is very simple in other DBMS with aggregation functions such as `LISTAGG`, `GROUP_CONCAT` or `STRING_AGG`. SQL Server doesn't feature such a function, but it can be emulated, which is quite awkward. Just google `string-aggregation sql-server` :-) — Thorsten Kettner, Mar 25 '20 at 06:56
Oops, update: SQL Server does have `STRING_AGG` as of version 2017: https://learn.microsoft.com/de-de/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15 — Thorsten Kettner, Mar 25 '20 at 07:19

score 2 · Accepted Answer · answered Mar 25 '20 at 07:25

You are looking for string aggregation. As of SQL Server 2017 this is available with STRING_AGG (https://learn.microsoft.com/de-de/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15):

SELECT
  bh.businessname, a.appointmentstarttime, a.appointmentendtime, a.appointmentstatus,
  a.appointmentfor, a.appointmentforid, a.appointmentstatus, bs.servicenames
FROM appointment a
JOIN businesshost bh ON bh.businesshostid = a.businesshostid
JOIN
(
  SELECT
    bhs.businesshostid,
    STRING_AGG(s.servicename, ', ') WITHIN GROUP (ORDER BY s.servicename) AS servicenames
  FROM businesshostservice bhs
  JOIN services s ON s.serviceid = bhs.serviceid
  GROUP BY bhs.businesshostid
) bs ON bs.businesshostid = bh.businesshostid
ORDER BY bh.businessname, a.appointmentstarttime;

Query to get distinct results

1 Answers1