SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 combine rows in one field (for xml)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

smailbox
Starting Member

Germany
1 Posts

Posted - 09/24/2013 :  01:51:33  Show Profile  Reply with Quote
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)

Singapore
17608 Posts

Posted - 09/24/2013 :  02:19:38  Show Profile  Reply with Quote

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



KH
Time is always against us

Go to Top of Page

VasiAnu
Starting Member

India
9 Posts

Posted - 09/24/2013 :  05:10:21  Show Profile  Reply with Quote
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
Go to Top of Page

VeeranjaneyuluAnnapureddy
Posting Yak Master

India
165 Posts

Posted - 09/25/2013 :  04:46:32  Show Profile  Reply with Quote
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

Edited by - VeeranjaneyuluAnnapureddy on 09/25/2013 04:56:32
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000