Search code examples
sqlsql-serverstring-aggregation

Query to get distinct results

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 |
+ -------------+----------------------+--------------------+-------------------+----------------+------------------+-------------------+------------------------------------+
Solution

  • 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;