sql server 2008 r2 - TSQL: FOR XML PATH('') Failing To Group -
i'm trying group column values specific column using for xml path('') in tsql. result in both cases (note without xml code - ie: select * @xml - same xml code):
class | animals ================================= asteroidea | starfish mammalia | dog mammalia | cat mammalia | coyote reptilia | crocodile reptilia | lizard according this article , this article (note second article leaves out group by, i'm unsure how author managed pull off without - i've tried , generates values), syntax should shown below this:
declare @xml table( animal varchar(50), class varchar(50) ) insert @xml values ('dog','mammalia') , ('cat','mammalia') , ('coyote','mammalia') , ('starfish','asteroidea') , ('crocodile','reptilia') , ('lizard','reptilia') select x1.class , stuff((select ',' + x2.animal [text()] @xml x2 x1.animal = x2.animal order x2.animal xml path('')),1,1,'' ) "animals" @xml x1 group class after few hours, between these examples , above code, fail see i'm wrong on syntax, i'm receiving error "column '@xml.animal' invalid in select list because not contained in either aggregate function or group clause." note if leave off group clause, still doesn't produce values in appropriate manner. set of eyes useful.
i think have where clause using wrong column, want use class not animal:
select x1.class , stuff((select ',' + x2.animal [text()] @xml x2 x1.class = x2.class order x2.animal xml path('')),1,1,'' ) "animals" @xml x1 group class see sql fiddle demo. result is:
| class | animals | --------------------------------- | asteroidea | starfish | | mammalia | cat,coyote,dog | | reptilia | crocodile,lizard |
Comments
Post a Comment