Блог

У меня есть таблица контактов клиентов и их роли. Упрощенный пример ниже.

 customer | role        | userid
----------------------------
1        | Support     | 123
1        | Support     | 456
1        | Procurement | 567
...
  

желаемый результат

 customer | Support1 | Support2 | Support3 | Support4 | Procurement1 | Procurement2
-----------------------------------------------------------------------------------
1        | 123      | 456      | null     | null     | 567          | null
2        | 123      | 456      | 12333    | 45776    | 888          | 56723
  

Таким образом, динамически создавайте количество требуемых столбцов в зависимости от того, сколько пользователей находятся в этой роли. Это небольшое количество ролей. Также я могу предположить максимум пользователя 5 в той же роли. Что означает, что в худшем случае мне нужно сгенерировать 5 столбцов для каждой роли. Идентификаторы пользователей не обязательно должны располагаться в каком-либо определенном порядке.

Мой текущий подход заключается в получении 1 идентификатора пользователя для каждой роли / клиента. Затем второй запрос извлекает другой идентификатор, который не был частью первого набора результатов. И так далее. Но таким образом я должен статически создать 5 запросов. Это работает. Но мне было интересно, есть ли более эффективный способ? Динамическое создание необходимых столбцов.

Пример извлечения одного пользователя для каждой роли:

 SELECT customer,role,
(SELECT  top 1 userid
FROM temp as tmp1
where tmp1.customer=tmp2.customer and tmp1.role=tmp2.role
) as userid
 FROM temp as tmp2
 group by customer,role
 order by customer,role
  

SQL создает с фиктивными данными

 create table temp
    (
      customer int,
      role nvarchar(20),
      userid int
    )
    
    insert into temp values (1,'Support',123)
    insert into temp values (1,'Support',456)
    insert into temp values (1,'Procurement',567)
    insert into temp values (2,'Support',123)
    insert into temp values (2,'Support',456)
    insert into temp values (2,'Procurement',888)
    insert into temp values (2,'Support',12333)
    insert into temp values (2,'Support',45776)
    insert into temp values (2,'Procurement',56723)
  

Возможно, вам потребуется немного адаптировать свой подход, если вы хотите избежать попадания в область программирования пользовательских табличных функций (что вам понадобится для динамической генерации столбцов). Вы не указываете, какой вариант базы данных SQL вы используете (SQL Server, PostgreSQL, ?). Я собираюсь сделать предположение, что он поддерживает некоторую форму функции агрегирования строк (они почти все делают), но синтаксис для этого будет отличаться, поэтому вам, вероятно, придется адаптировать код к вашим обстоятельствам. Вы упоминаете, что количество ролей невелико (5-иш?). Предлагаемое решение заключается в создании списка идентификаторов пользователей, разделенных запятыми, по одному для каждой роли, с использованием общих табличных выражений (CTE) и функции LISTAGG (с различными именами STRING_AGG, GROUP_CONCAT и т. Д. В других базах данных).

 WITH tsupport 
     AS (SELECT customer, 
                Listagg(userid, ',') AS "Support" 
         FROM   temp 
         WHERE  ROLE = 'Support' 
         GROUP  BY customer), 
     tprocurement 
     AS (SELECT customer, 
                Listagg(userid, ',') AS "Procurement" 
         FROM   temp 
         WHERE  ROLE = 'Procurement' 
         GROUP  BY customer)
 --> tnextrole... 
 --> AS (SELECT ... for additional roles 
 -->            Listagg...
SELECT a.customer, 
       "Support", 
       "Procurement" 
 -->   "Next Role"  etc.
FROM   tsupport a 
       JOIN tprocurement b 
         ON a.customer = b.customer 
 -->   JOIN tNextRole ...
  

Fiddle здесь с результатом, который отображается, как показано ниже, на основе ваших фиктивных данных: