CODEWITHSUNDEEP
sqlsql-server
StackItEasy
StackItEasy

Reputation: 19

How do I select specific names of a column with Content?

Hello I've got a problem with SQL Server 2012, 

SELECT e.especes,COUNT(*) AS [nombre de sujets],
    STUFF((SELECT  ',' + ' ' + a.nom 
              FROM  animal a,espece e
              where e.IDESPECE = a.IDESPECE
              FOR XML PATH (''))
              , 1, 1, '')  AS noms
    INTO statEspece 
    from espece e,animal a 
    WHERE e.IDESPECE = a.IDESPECE 
    GROUP BY a.IDESPECE, e.especes;

Even if i try to put this line:

where e.IDESPECE = a.IDESPECE

the name column is filled with all the animals of the dbo.animal.nom .

The output looks like this :

Animal  Number   Names
Tigre   2   Tigrou, Chita, Chloe, Jumbo, Zaza, Coco, Kahn, King
Lion    1   Tigrou, Chita, Chloe, Jumbo, Zaza, Coco, Kahn, King
Zèbre   1   Tigrou, Chita, Chloe, Jumbo, Zaza, Coco, Kahn, King
Singe   2   Tigrou, Chita, Chloe, Jumbo, Zaza, Coco, Kahn, King
other   1   Tigrou, Chita, Chloe, Jumbo, Zaza, Coco, Kahn, King
other   1   Tigrou, Chita, Chloe, Jumbo, Zaza, Coco, Kahn, King

Can anybody please explain me why it returns me all the names in the database?

Upvotes: 0

Views: 79

Answers (1)

Jayvee
Jayvee

Reputation: 10875

try this:

SELECT e.especes,COUNT(*) AS [nombre de sujets],
STUFF((SELECT  ',' + ' ' + a1.nom 
          FROM  animal a1,espece e1
          where e1.IDESPECE = a1.IDESPECE AND a1.IDESPECE=a.IDESPECE
          FOR XML PATH (''))
          , 1, 1, '')  AS noms
INTO statEspece 
from espece e,animal a 
WHERE e.IDESPECE = a.IDESPECE 
GROUP BY a.IDESPECE, e.especes;

Upvotes: 1

Related Questions