FOR XML concatenation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

FOR XML concatenation

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-03-03 : 13:22:17

Greetings
As you can see in these rows from my table I want to be able to return one row with all productcodes concatenated for the same type and form


ProductCode   Type         Form
8244          C	           YA
8504          C	           YA
8554	      C	           YA
8614	      C	           YA
8634	      C	           YA

I am trying out the FOR XML Path but no success.
Thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-03 : 13:40:16

May be this?

declare @t table (col1 char(20), col2 char(20), col3 char(20))
insert @t
select '8244','C','YA' union all
select '8504','C','YA' union all
select '8554','C','YA'

select col2,col3,
max(case col1 when 8244 then col1 else 0 end) as productcode1,
max(case col1 when 8504 then col1 else 0 end) as productcode2,
max(case col1 when 8554 then col1 else 0 end) as productcode3
from @t group by col2,col3

Result
C YA 8244 8504 8554

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-03-03 : 13:56:23

thanks vijay but there is a way of doing dynamically with the FOR XML PATH method.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-03 : 23:21:42

try this by using xml path
select distinct col2,col3,stuff((select ','+col1 from @T where col2 = t.col2 and col3 = t.col3 for xml path('')) ,1,1,'')
from @t t

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-03 : 23:28:01

Can u post ur expected Output?

malawneh
Starting Member

24 Posts

Posted - 2009-03-05 : 10:46:06

declare @t table (col1 char(20), col2 char(20), col3 char(20))
insert @t
select '8244','C','YA' union all
select '8504','C','YA' union all
select '8554','C','YA'

SELECT * FROM @t

DECLARE @ProductList VarChar(1000)

SELECT @ProductList = STUFF((SELECT ', ' + RTRIM(Col1)
FROM @t
WHERE Col2 = t.Col2
FOR XML PATH('')),1,2,'')
FROM @t t
WHERE Col2 = 'C'

SELECT @ProductList

Output...
8244, 8504, 8554

(1 row(s) affected)

Michael Alawneh, DBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources