I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 218, comments - 2281, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

Column values Concat the SQL Server 2005 way

Here's an interesting way of contencating values in one column. It makes use of the new XML capabilities.

use master

select column_name as col1 FROM INFORMATION_SCHEMA.Columns WHERE table_name = 'spt_values'

select (select column_name as col1 FROM INFORMATION_SCHEMA.Columns WHERE table_name = 'spt_values' for xml raw, elements, type).query(' for $col1 in (row/col1) return concat($col1, ",")')

Order can be forced by using

       'for $col1 in (row/col1)
        order by $col1 ascending
        return concat($col1, ",")'

For descending order change ascending to descending.

Print | posted on Friday, June 02, 2006 11:47 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: Column values Concat the SQL Server 2005 way

Well this was featured on
"the WHERE clause" SQL Server podcast on SSWUG.ORG
here
http://sswug.org/sswugradio/pdetails.asp?pid=112

seems that Chuck Boyce (http://chuckboyce.blogspot.com) likes this :))

thanx Chuck!
6/7/2006 6:06 PM | Mladen
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET