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)
 Need to combine rows

Author  Topic 

GaryNull
Starting Member

14 Posts

Posted - 2012-10-14 : 15:42:26
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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-14 : 16:10:37
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

14 Posts

Posted - 2012-10-17 : 22:16:26
thanks sunitabeck,
very clever, worked perfectly




Go to Top of Page
   

- Advertisement -