tag:blogger.com,1999:blog-495177919198719500.post5418386675597511740..comments2023-12-27T20:52:29.483-08:00Comments on Microsoft Sql Server Tutorials: Row set Concatenation - SQL Server vs MySQLSuprotim Agarwalhttp://www.blogger.com/profile/08349831623922214390noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-495177919198719500.post-38035956881196794342012-09-10T05:56:47.203-07:002012-09-10T05:56:47.203-07:00If you are able to deploy CLR functions on your in...If you are able to deploy CLR functions on your instance, I highly recommend adding a concatenate function to your database. http://www.mssqltips.com/sqlservertip/2022/concat-aggregates-sql-server-clr-function/<br /><br />This would reduce your example down from:<br /> declare @names varchar(8000) <br /> set @names='' <br /> select distinct id, <br /> stuff((select (','+@names+names) from test as t2 where t1.id=t2.id for xml path('')),1,1,'') as names <br /> from <br /> test as t1<br /><br />to:<br /> select id, <br /> dbo.concat(names, ',') as names <br /> from test<br /> group by id<br /><br />An added bonus is that it doesn't leave any trailing delimiters.<br /><br />starsky51https://www.blogger.com/profile/16961839349002878806noreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-75029476499625506862012-09-10T04:26:51.599-07:002012-09-10T04:26:51.599-07:00Thanks Alex Peta for your feedback
ChrisM, this c...Thanks Alex Peta for your feedback<br /><br />ChrisM, this code runs from version 2005 onwards. Nothing new related to row concatenation in version 2012Madhivananhttp://www.beyondrelational.com/blogs/madhivanannoreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-36043825068281202082012-09-10T04:12:19.656-07:002012-09-10T04:12:19.656-07:00thanks alex for an Oracle solution!
@madhivanan w...thanks alex for an Oracle solution!<br /><br />@madhivanan which SQL server version does this code run on. Anything new in SQL 2012 related to row concat?ChrisMnoreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-8372270910924098672012-09-10T02:51:32.565-07:002012-09-10T02:51:32.565-07:00In oracle there are a couple of ways to do this.
...In oracle there are a couple of ways to do this.<br /><br />The simplest are:<br /><br />WM_CONCAT() this is an undocumented function -- its the same as the mySQL equivalent.<br /><br />The recomended official version is to use LISTAGG() functionAlex Petahttp://alexpeta.ronoreply@blogger.com