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

Popular posts from this blog

ios - iPhone/iPad different view orientations in different views , and apple approval process -

php - HTTP_REFERER woes: How can I allow access to a specific page, only when a visitor has visited another specific page beforehand? -

java Extracting Zip file -