TSQL:FOR XML PATH('')无法分组 [英] TSQL: FOR XML PATH('') Failing To Group

问题描述

我正在尝试使用TSQL中的FOR XML PATH('')按特定列对列值进行分组.这是在两种情况下的结果(请注意，没有XML代码-即:SELECT * FROM @xml-与具有XML代码的相同):

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

几个小时后，在这些示例和上面的代码之间，我看不到语法错误，但是我收到错误消息列'@ xml.Animal'在选择列表中无效，因为它既不包含在聚合函数中，也不包含在GROUP BY子句中."请注意，如果我忽略了GROUP BY子句，它仍然不会以适当的方式产生值.另一双眼睛会很有用.

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.

推荐答案

我认为您的WHERE子句使用了错误的列，您想使用Class而不是Animal:

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

请参见带演示的SQL提琴.结果是:

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

这篇关于TSQL:FOR XML PATH('')无法分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！