Как создать список через запятую с помощью SQL-запроса?

Question

Как создать список через запятую с помощью SQL-запроса?

У меня есть 3 таблицы под названием:

приложения (id, имя)

ресурсы (id, имя)

ApplicationsResources (id, app_id, resource_id)

Я хочу показать на GUI таблицу всех имен ресурсов. В одной ячейке в каждой строке я хотел бы перечислить все приложения (через запятую) этого ресурса.

Итак, вопрос в том, что это лучший способ сделать это в SQL, поскольку мне нужно получить все ресурсы, и я также нужно получить все приложения для каждого ресурса?

должен ли я сначала запустить select * from resources, а затем выполнить цикл по каждому ресурсу и сделать отдельный запрос на ресурс, чтобы получить список приложений для этого ресурса?

есть ли способ сделать это в одном запросе?

10260 11

sql-server sql tsql string-aggregation

11 ответов:

Comments

Ничего не найдено.

Dmytrii Nagirniak · Accepted Answer · 2016-10-04 22:26:04

нет никакого способа сделать это в DB-агностическим способом. Поэтому вам нужно получить весь набор данных следующим образом:
select 
  r.name as ResName, 
  a.name as AppName
from 
  Resouces as r, 
  Applications as a, 
  ApplicationsResources as ar
where
  ar.app_id = a.id 
  and ar.resource_id = r.id
а затем конкат Имя_приложенияпрограммно при группировке по ResName.

OMG Ponies · Accepted Answer · 2017-08-31 14:32:14

MySQL

  SELECT r.name,
         GROUP_CONCAT(a.name SEPARATOR ',')
    FROM RESOURCES r
    JOIN APPLICATIONSRESOURCES ar ON ar.resource_id = r.id
    JOIN APPLICATIONS a ON a.id = ar.app_id
GROUP BY r.name

SQL Server (2005+)

SELECT r.name,
       STUFF((SELECT ','+ a.name
               FROM APPLICATIONS a
               JOIN APPLICATIONRESOURCES ar ON ar.app_id = a.id
              WHERE ar.resource_id = r.id
           GROUP BY a.name
            FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '')
 FROM RESOURCES r

SQL Server (2017+)

  SELECT r.name,
         STRING_AGG(a.name, ',')
    FROM RESOURCES r
    JOIN APPLICATIONSRESOURCES ar ON ar.resource_id = r.id
    JOIN APPLICATIONS a ON a.id = ar.app_id
GROUP BY r.name

Oracle

рекомендую чтение о агрегации строк / конкатенации в Oracle.

Robert Harvey · Accepted Answer · 2012-08-07 18:54:32

использование COALESCE для построения строки с разделителями-запятыми в SQL Server
http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string

пример:
DECLARE @EmployeeList varchar(100)

SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') + 
   CAST(Emp_UniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1

SELECT @EmployeeList

marc_s · Accepted Answer · 2017-04-10 14:41:07

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

для SQL Server 2005 и выше, вы можете использовать:
SELECT
     r.ID, r.Name,
     Resources = STUFF(
       (SELECT ','+a.Name
        FROM dbo.Applications a
        INNER JOIN dbo.ApplicationsResources ar ON ar.app_id = a.id
        WHERE ar.resource_id = r.id
        FOR XML PATH('')), 1, 1, '')
FROM
     dbo.Resources r
он использует SQL Server 2005 FOR XML PATH построить список подэлементов (приложений для данного ресурса) в виде списка через запятую.

Марк

Jamie Wong · Accepted Answer · 2010-06-21 22:22:03

Я считаю, что вы хотите:

SELECT ItemName, GROUP_CONCAT(DepartmentId) FROM table_name GROUP BY ItemName

Если вы используете MySQL

ссылка

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

Kenneth · Accepted Answer · 2010-06-21 22:25:22

предполагая, что SQL Server:

структура таблицы:

CREATE TABLE [dbo].[item_dept](
    [ItemName] char(20) NULL,
    [DepartmentID] int NULL   
)

запрос:

SELECT ItemName,
       STUFF((SELECT ',' + rtrim(convert(char(10),DepartmentID))
        FROM   item_dept b
        WHERE  a.ItemName = b.ItemName
        FOR XML PATH('')),1,1,'') DepartmentID
FROM   item_dept a
GROUP BY ItemName

результаты:

ItemName    DepartmentID
item1       21,13,9,36
item2       4,9,44

user1696097 · Accepted Answer · 2012-09-25 06:31:31

Я думаю, что мы могли бы написать следующим образом, чтобы получить (ниже код просто пример, пожалуйста, измените по мере необходимости):

Create FUNCTION dbo.ufnGetEmployeeMultiple(@DepartmentID int)
RETURNS VARCHAR(1000) AS

BEGIN

DECLARE @Employeelist varchar(1000)

SELECT @Employeelist = COALESCE(@Employeelist + ', ', '') + E.LoginID
FROM humanresources.Employee E

Left JOIN humanresources.EmployeeDepartmentHistory H ON
E.BusinessEntityID = H.BusinessEntityID

INNER JOIN HumanResources.Department D ON
H.DepartmentID = D.DepartmentID

Where H.DepartmentID = @DepartmentID

Return @Employeelist

END

SELECT D.name as Department, dbo.ufnGetEmployeeMultiple (D.DepartmentID)as Employees
FROM HumanResources.Department D

SELECT Distinct (D.name) as Department, dbo.ufnGetEmployeeMultiple (D.DepartmentID) as 
Employees
FROM HumanResources.Department D

Martin Smith · Accepted Answer · 2016-11-19 13:10:39

С следующая версия SQL Server вы сможете делать
SELECT r.name,
       STRING_AGG(a.name, ',')
FROM   RESOURCES r
       JOIN APPLICATIONSRESOURCES ar
         ON ar.resource_id = r.id
       JOIN APPLICATIONS a
         ON a.id = ar.app_id
GROUP  BY r.name 
для предыдущих версий продукта существует довольно широкий спектр различных подходов к этой проблеме. Отличный обзор о них есть в статье: объединение значений строк в Transact-SQL.

объединение значений, когда количество элементов неизвестно

рекурсивный метод CTE

черный ящик Методы XML

Использование Общеязыковой Среды Выполнения

скалярную функцию с рекурсией

таблица с значением UDF с циклом WHILE

динамический SQL

приближение Курсора
.

ненадежные подходы

скалярный UDF с расширением обновления t-SQL

скалярный UDF с переменной конкатенацией в SELECT

Pavel Zimogorov · Accepted Answer · 2016-06-27 22:33:52

MySQL

  SELECT r.name,
         GROUP_CONCAT(a.name SEPARATOR ',')
    FROM RESOURCES r
    JOIN APPLICATIONSRESOURCES ar ON ar.resource_id = r.id
    JOIN APPLICATIONS a ON a.id = ar.app_id
GROUP BY r.name

**

MS SQL Server

SELECT r.name,
       STUFF((SELECT ','+ a.name
               FROM APPLICATIONS a
               JOIN APPLICATIONRESOURCES ar ON ar.app_id = a.id
              WHERE ar.resource_id = r.id
           GROUP BY a.name
            FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '')
 FROM RESOURCES r
 GROUP BY deptno;

Oracle

  SELECT r.name,
         LISTAGG(a.name SEPARATOR ',') WITHIN GROUP (ORDER BY a.name)
  FROM RESOURCES r
        JOIN APPLICATIONSRESOURCES ar ON ar.resource_id = r.id
        JOIN APPLICATIONS a ON a.id = ar.app_id
  GROUP BY r.name;

Don · Accepted Answer · 2009-11-30 07:56:40

агностиком, и плоскодонки.
Select a.name as a_name, r.name as r_name
  from ApplicationsResource ar, Applications a, Resources r
 where a.id = ar.app_id
   and r.id = ar.resource_id
 order by r.name, a.name;
теперь пользователь вашего языка программирования сервера для объединения a_names в то время как r_name такой же, как в прошлый раз.

Abe Miessler · Accepted Answer · 2010-06-21 22:20:12

Это будет сделано в SQL Server:

DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' ,'') + Convert(nvarchar(8),DepartmentId)
FROM Table
SELECT @listStr