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

根据这篇文章和这篇文章(注意第二篇文章省略了GROUP BY，我不确定作者是如何在没有它的情况下成功实现这一点的——我试过了，它只生成所有值)，语法应该如下所示:

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 而不是 <代码>动物:

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 Fiddle with Demo.结果是:

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

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