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)
 Need to combine rows
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

GaryNull
Starting Member

USA
14 Posts

Posted - 10/14/2012 :  15:42:26  Show Profile  Reply with Quote
Hello,
Is there a way to combine rows from one table
into just one row in a new table.
I have this BulletFile table :

sku bullet_point position

GE31-002103 Fine edge blade 1
GE31-002103 Tactile Rubber Grip 2
GE31-002103 Buckel included 3

and I want to put all those rows into one field called
Bullets in the Products table, like this :

<ul>
<li>Fine edge blade</li>
<li>Tactile Rubber Grip</li>
<li>Buckel included</li>
</ul>

Some rows in the BulletFile table have more than 3 bullets.
They also have a "postion" field if that helps and can be
used to accomplish this.

UPDATE Product
SET Bullets =

'<ul><li>'
+ [bullet_1]
+ '</li><li>'
+ [bullet_2]
+ '</li><li>'
+ [bullet_3]
+ '</li><li>'
+ [bullet_4, bullet_5, bullet_6, etc . . if more bullets]
+ '</li></ul>'

WHERE ??

Any ideas on how to do this? thanks

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/14/2012 :  16:10:37  Show Profile  Reply with Quote
You can use string manipulation, but it may be easier and simpler to use XML features that SQL Server offers. To select the data that you described run this query:
SELECT
	a.sku,
	b.Bullets
FROM
	(SELECT DISTINCT sku FROM Product) a
	OUTER APPLY
	(
		SELECT bullet_point AS [li]
		FROM Product b
		WHERE b.sku = a.sku
		FOR XML PATH('ul')
	) b(Bullets)
If that looks like what you want, then you can use that as a subquery to do the update, as shown below:
UPDATE p SET 
Bullets = s.Bullets 
FROM 
(
	SELECT
		a.sku,
		b.Bullets
	FROM
		(SELECT DISTINCT sku FROM Product) a
		OUTER APPLY
		(
			SELECT bullet_point AS [li]
			FROM Product b
			WHERE b.sku = a.sku
			FOR XML PATH('ul')
		) b(Bullets)
) s 
INNER JOIN Product p ON p.sku = s.sku;
Go to Top of Page

GaryNull
Starting Member

USA
14 Posts

Posted - 10/17/2012 :  22:16:26  Show Profile  Reply with Quote
thanks sunitabeck,
very clever, worked perfectly




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.19 seconds. Powered By: Snitz Forums 2000