Comment puis-je créer une liste séparée par des virgules à l'aide d'une requête SQL?

J'ai 3 tables appelé:

Applications (id, nom)
Ressources (id, nom)
ApplicationsResources (id, app_id, resource_id)

Je veux afficher sur une interface graphique, un tableau de tous les noms de ressource. Dans une cellule de chaque ligne je voudrais la liste de toutes les applications (séparés par des virgules) de la ressource.

La question est donc, quelle est la meilleure façon de le faire en SQL que j'ai besoin d'obtenir toutes les ressources, et j'ai aussi besoin d'obtenir toutes les applications pour chaque ressource?

Dois-je exécuter un select * from ressources d'abord et ensuite une boucle sur chaque ressource et faire une requête distincte pour chaque ressource pour obtenir la liste des applications pour cette ressource?

Est-il une manière que je peux le faire en une seule requête?

La base de données que vous utilisez?
je voudrais que ma solution pour être agnostique db
La manipulation des chaînes n'est pas très db agnostique.
Voir aussi stackoverflow.com/questions/1048209/...
Ce produit de base de données et la version?
Il est sql server 2008
Vous avez peut-être nouveau, DONC n'oubliez pas de upvote des réponses utiles et d'accepter celui qui est le plus utile pour vous.

InformationsquelleAutor leora | 2009-11-30

0

Il n'y a aucun moyen de le faire dans la base de données de façon agnostique.
Si vous avez besoin d'obtenir l'ensemble des données comme ceci:
```
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
```
Et alors concat la AppName par programme, lors du regroupement par ResName.

InformationsquelleAutor Dmytrii Nagirniak

115

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

Je recommande lecture sur la chaîne d'agrégation/concaténation dans Oracle.

J'ai mis à jour la syntaxe pour l'utilisation correcte de GROUP_CONCAT.
SQL Server a rendu difficile pour aucune raison
Il faut également noter que group_concat par défaut ',' séparées, de sorte group_concat(un.nom) est équivalent à group_concat(un.nom du SÉPARATEUR ',')
SQLite est le même que MySQL à l'exception de la fonction ressemble à: GROUP_CONCAT(a.name, ', ')

InformationsquelleAutor OMG Ponies

26

À l'aide de FUSIONNER pour Construire la Chaîne Délimitée par des Virgules dans SQL Server

http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string

Exemple:
```
DECLARE @EmployeeList varchar(100)

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

SELECT @EmployeeList
```
- cela semble une bien meilleure solution que la plus grande puissance de réponses. Facile et polyvalent.
- Que faire si vous voulez EmployeeList par (Groupe de) ministère?
- Puis ajouter une clause GROUP BY.
InformationsquelleAutor Robert Harvey
11

Je ne sais pas si il n'y a aucune solution pour le faire dans une base de données de façon agnostique, car vous allez probablement avoir besoin d'une forme de manipulation de chaîne, et ceux-ci sont en général différentes entre les vendeurs.

Pour SQL Server 2005, vous pouvez utiliser:
```
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
```
Il utilise le SQL Server 2005 FOR XML PATH construire à la liste de points (les demandes pour une ressource donnée) comme une liste séparée par des virgules.

Marc
- Je crois que vous manque un "r". La JOINTURE INTERNE doit dire "SUR l'ar.app_id = un.id". Fonctionne pour moi maintenant. Merci.
- Merci pour vos "yeux d'aigle" spotting cette faute de frappe! Fixe.
InformationsquelleAutor marc_s
5

Je crois que ce que vous voulez, c'est:

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

Si vous utilisez MySQL

Référence
- http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
- GROUP_CONCAT ne fonctionne que dans MySQL et SQLite. L'OP a dit dans un commentaire qu'ils sont à l'aide de MS SQL Server 2008.
- Ne pas voir le commentaire jusqu'à ce que après le post. Dois-je supprimer ma réponse pour le manque de pertinence ou de congé juste pour l'intérêt?
- ...des années plus tard, mais pour l'enregistrement... je l'avais quitter, il est utile & les OP le titre est ambigu. J'étais à la recherche d'une solution pour MySQL, cette question est venu (il est étiqueté comme mysql) & cette réponse m'a aidé!
- Votre réponse vient dans les serps pour mysql. M'a sauvé heures - merci de laisser il en.
InformationsquelleAutor Jamie Wong

En supposant que SQL Server:

Structure de la Table:

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

Requête:

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

Résultats:

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

+1 Cela fonctionne, mais à mon humble avis cette solution for SQL Server est un hack, c'est extrêmement laid et non intuitive. Pas de votre faute si. 🙂
Il y a une autre solution SQL Server possible avec la coutume CLR agrégats de cas de garantie d'Ordre de la délimité par des éléments n'est pas indispensable.

InformationsquelleAutor Kenneth

Je pense que nous pourrions écrire de la façon suivante pour récupérer(code ci-dessous est juste un exemple, s'il vous plaît modifier si nécessaire):

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

InformationsquelleAutor user1696097

4

De la prochaine version de SQL Server vous serez en mesure de faire
```
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 
```
Pour les versions précédentes du produit il y a une assez grande variété de différentes approches à ce problème. Une excellente revue d'entre eux est dans l'article: Concaténant les Valeurs de Ligne de Transact-SQL.
- De la concaténation des valeurs lorsque le nombre d'éléments ne sont pas connus
  - Expression de table commune récursive méthode
  - La blackbox méthodes XML
  - À L'Aide De Common Language Runtime
  - Scalaire UDF avec la récursivité
  - Table UDF avec une boucle WHILE
  - SQL dynamique
  - Le Curseur approche
    
    .
- Non-fiable approches
  - Scalaire UDF avec t-SQL de mise à jour de l'extension
  - Scalaire UDF avec la variable de la concaténation SÉLECTIONNEZ
InformationsquelleAutor Martin Smith

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;

InformationsquelleAutor Pavel Zimogorov

Être agnostique, goutte dos et plate.

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;

Maintenant l'utilisateur de votre serveur langage de programmation pour concaténer a_names tout r_name est le même que la dernière fois.

InformationsquelleAutor Don

1

Cela fera dans SQL Server:
```
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' ,'') + Convert(nvarchar(8),DepartmentId)
FROM Table
SELECT @listStr
```
- Il ne va pas le faire pour chaque GROUPE si. Pour cela, vous devez ajuster marc_s de répondre à la double question stackoverflow.com/questions/1817985/... (ou voir Kenneth réponse à cette question)
InformationsquelleAutor Abe Miessler

Vous devez vous connecter pour publier un commentaire.