TSQL: FOR XML PATH('') Failing To Group

for-xml-path, sql-server-2008-r2, t-sql

Solution

I think you have your `WHERE` clause using the wrong column, you want to use `Class` not `Animal`:

SELECT x1.Class
    , STUFF((SELECT ',' + x2.Animal AS [text()] 
    FROM @xml x2 
    WHERE x1.Class = x2.Class 
    ORDER BY x2.Animal 
    FOR XML PATH('')),1,1,'' ) AS "Animals"
FROM @xml x1
GROUP BY Class

See SQL Fiddle with Demo. The result is:

|      CLASS |          ANIMALS |
---------------------------------
| Asteroidea |         Starfish |
|   Mammalia |   Cat,Coyote,Dog |
|   Reptilia | Crocodile,Lizard |

Problem

I'm trying to group column values by a specific column using `FOR XML PATH('')` in TSQL. This is the result in both cases (note that the without XML code - ie: `SELECT * FROM @xml` - is the same as the with XML code): ``` Class | Animals ================================= Asteroidea | Starfish Mammalia | Dog Mammalia | Cat Mammalia | Coyote Reptilia | Crocodile Reptilia | Lizard ``` According to this article and this article (note that the second article leaves out the `GROUP BY`, which I'm unsure how the author managed to pull this off without it - I've tried and it only generates all the values), the syntax should be as shown below this: ``` DECLARE @xml TABLE( Animal VARCHAR(50), Class VARCHAR(50) ) INSERT INTO @xml VALUES ('Dog','Mammalia') , ('Cat','Mammalia') , ('Coyote','Mammalia') , ('Starfish','Asteroidea') , ('Crocodile','Reptilia') , ('Lizard','Reptilia') SELECT x1.Class , STUFF((SELECT ',' + x2.Animal AS [text()] FROM @xml x2 WHERE x1.Animal = x2.Animal ORDER BY x2.Animal FOR XML PATH('')),1,1,'' ) AS "Animals" FROM @xml x1 GROUP BY Class ``` After a few hours, between these examples and the above code, I fail to see where I'm wrong on syntax, but I'm receiving the error "Column '@xml.Animal' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." Note that if I leave off the GROUP BY clause, it still doesn't produce the values in the appropriate manner. Another set of eyes would be useful.

Original source