combine rows in one field (for xml)

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 2008 Forums

Transact-SQL (2008)

combine rows in one field (for xml)

Author

Topic

smailbox
Starting Member

1 Post

Posted - 2013-09-24 : 01:51:33

Hi There,

this is my first entry in this forum so lets say "Hello" to you all :-)
For the beginning I have a question to you which makes me desperate.

This ist what I have:
(1 table,3 colums)
ID NAME VALUE
1 Colour green
1 Material paper
1 Vendor ZZZ
2 Colour red

Now this should look like this:
ID ATTRIBUTES
1 Colour|green;Material|paper;Vendor|ZZZ
2 Colour|red

So if anyone of you have a hint for me how I could make this in a sql statement please tell me.

Thanks a lot
Michael

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-09-24 : 02:19:38

[code]
select ID, ATTRIBUTES = MAX(STUFF(ATTRIBUTES, 1, 1, ''))
from table1 t
cross apply
(
select ';' + NAME + '|' + VALUE
from table1 x
where x.ID = t.ID
for xml path('')
) a (ATTRIBUTES)
group by ID[/code]

KH
[spoiler]Time is always against us[/spoiler]

VasiAnu
Starting Member

9 Posts

Posted - 2013-09-24 : 05:10:21

Select DISTINCT ID,STUFF((Select ',' + E2.Name + ' | ' + E2.Value from Table1 E2 where E1.ID = E2.ID for xml path('')),1,1,'') from Table1 E1

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-09-25 : 04:46:32

SELECT DISTINCT T2.ID
, (SELECT STUFF((SELECT ','+CAST(Name AS VARCHAR)+' | '+CAST(Value AS VARCHAR)
FROM (SELECT Name,Value FROM Tem25 AS T1
WHERE T1.Id = T2.Id)x
ORDER BY Name,Value
FOR XML PATH('')),1,1,'')) AS 'Description'
FROM Tem25 AS T2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources