Как сделать так, чтобы вывод в STRING_AGG() не повторялся?

Рейтинг: 0Ответов: 1Опубликовано: 10.01.2023
SELECT
    Customer as [CustomerFullName]
    , STRING_AGG(Post.Postman, ', ') as Postmans
FROM Delivery as Deli
INNER JOIN Subscription as Subs
    ON Subs.SubscriptionID = Deli.SubscriptionID
INNER JOIN (SELECT CustomerID, CONCAT(FirstName, ' ', LastName) as Customer 
    FROM Customer) as Cust
    ON Subs.CustomerID = Cust.CustomerID
INNER JOIN (SELECT PostmanID, CONCAT(FirstName, ' ', LastName) as Postman
            FROM Postman) as Post
    ON Deli.PostmanID = Post.PostmanID
WHERE Cust.CustomerID = '3'
Group by Cust.Customer;

На картинке вывод с повторением имён, хотелось бы от этого избавиться введите сюда описание изображения

sql
Источник: Stack Overflow на русском

Ответы

▲ 0
-- тестовые данные
;with Delivery as (
  select SubscriptionID, PostmanID
  from (
    values
      (1,1),
      (1,2)
  ) v(SubscriptionID, PostmanID)
), Subscription as (
  select 1 SubscriptionID, 3 CustomerID
), Customer as (
  select 3 CustomerID, 'A' FirstName, 'B' LastName
), Postman as (
  select PostmanID, FirstName, LastName
  from (
    values
      (1, 'Aa', 'Bb'),
      (2, 'Cc', 'Dd'),
      (1, 'Aa', 'Bb')
  ) v(PostmanID, FirstName, LastName)
)
-- основной запрос
select
  f_cust_names.value CustomerFullName,
  string_agg(f_post_names.value, ', ') Postmans
from Delivery Deli
join Subscription Subs ON Subs.SubscriptionID = Deli.SubscriptionID
join Customer Cust ON Cust.CustomerID=Subs.CustomerID and Cust.CustomerID = 3
cross apply (
  select
    concat(Cust.FirstName, ' ', Cust.LastName)
) f_cust_names(value)
cross apply (
  select distinct concat(p.FirstName, ' ', p.LastName)
  from Postman p
  where p.PostmanID=Deli.PostmanID
) f_post_names(value)
group by f_cust_names.value

CustomerFullName Postmans
---------------- ------------------
A B              Aa Bb, Cc Dd