I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 219, comments - 2287, 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

SQL Server 2005: Concat values XML Style

A few days ago i showed how to split string with XML.

Now it's time for concatenation with XML.

 

DECLARE @t TABLE (col VARCHAR(10))

INSERT into @t
select 'aaaa' UNION ALL
select 'bbbb' UNION ALL
select 'cccc' UNION ALL
select null UNION ALL
select 'dddd'

SELECT * FROM @t

SELECT 
(
    SELECT  col + ', ' as [text()]
    FROM    @t
    ORDER BY col DESC
    FOR XML PATH('') 
) AS MyCsvList


Both of these (split and concat) methods aren't really anything special. They're just a new way of solving old problem.

But whenever i need them i have to look them up, so where else to keep them handy than here, right? :)

 


kick it on DotNetKicks.com

Print | posted on Friday, June 01, 2007 9:49 AM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: SQL Server 2005: Concat values XML Style

If you want to get rid of the last comma

SELECT LEFT(MyCsvList,LEN(MyCsvList)-1)
FROM (SELECT
(
SELECT col + ', ' as [text()]
FROM @t
ORDER BY col DESC
FOR XML PATH('')
) AS MyCsvList) z


Denis
6/1/2007 11:11 AM | Denis the SQL Menace
Gravatar

# re: SQL Server 2005: Concat values XML Style

thanx D-Man :)

I usually just trim it on the client, though.
6/1/2007 11:13 AM | Mladen
Gravatar

# re: SQL Server 2005: Concat values XML Style

Yes, because we all know what Celko says "Formatting should be done on the client" ;-)
6/1/2007 12:07 PM | Denis the SQL Menace
Gravatar

# re: SQL Server 2005: Concat values XML Style

Also see
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254
for more formatting stuff with FOR XML PATH
12/9/2007 10:57 PM | Peso
Gravatar

# re: SQL Server 2005: Concat values XML Style

Wow! You saved my life!
10/14/2008 3:12 PM | Dejan
Gravatar

# re: SQL Server 2005: Concat values XML Style

Be careful with the For XML Path as it replaces charachters like < and > with &lt and &gt.
11/14/2008 11:50 PM | A
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET