The recursive coalesce challenge

Question

I have a table with Product records and Model records. Both the product and model records has a QTY and Size column. For a given product_id, I would like to grab all qtys and sizes from the Model records, and place them in to the single* qty and size field for the **parent Product record, delimited by a pipe "|". Here is the sql I have so far. It successfully grabs the size and qty values from the Model records for a given product_id, but I am unable to get it to update the parent Product record fields, and do it recursively. Can someone give me a hand? Thx

This example uses product_id '26206'

--declare local variables
DECLARE
      @size_DelimitedString VARCHAR(MAX),
      @qty_DelimitedString VARCHAR(MAX)

 --pivot rows into delimited string
SELECT
      @size_DelimitedString = COALESCE(@size_DelimitedString, '') + CONVERT(VARCHAR(10), [BD New Product Data].model_size) + '|'
FROM
      [BD New Product Data]
      where record_type = 'model' and product_id = '26206'

 SELECT
      @qty_DelimitedString = COALESCE(@qty_DelimitedString, '') + CONVERT(VARCHAR(10), [BD New Product Data].model_quantity) + '|'
FROM
      [BD New Product Data]
      where record_type = 'model' and product_id = '26206'

--trim off last pipe
SET @size_DelimitedString = (SELECT SUBSTRING(@size_DelimitedString, 1, LEN(@size_DelimitedString)-1))
SET @qty_DelimitedString = (SELECT SUBSTRING(@qty_DelimitedString, 1, LEN(@qty_DelimitedString)-1)) 

--show results  
SELECT @size_DelimitedString 
SELECT @qty_DelimitedString

What RDBMS and version? Oracle supports WM_CONCAT which would do this easily in versions 10g or earlier, and LIST_AGG or XML_AGG which eliminates the recursion part by combining multiple rows into 1. SQL Server which is my guess as to what this is... uses XML Path which is a bit more complex but would work [example](http://stackoverflow.com/questions/12559551/sql-server-equivalent-of-wm-concat-function) — xQbert, Apr 11 '13 at 02:23
I am using MS SQL 2012.Microsoft SQL Server Management Studio 11.0.2100.60 Microsoft Data Access Components (MDAC) 6.1.7601.17514 Microsoft MSXML 3.0 4.0 5.0 6.0 Microsoft Internet Explorer 9.10.9200.16540 Microsoft .NET Framework 4.0.30319.296 Operating System 6.1.7601 — user2268505, Apr 11 '13 at 02:34
No pressure at all, but http://meta.stackexchange.com/questions/10672/encourage-users-to-select-accepted-answer-for-old-questions/10690#10690. :) — bob, May 11 '13 at 02:55

score 0 · Answer 1 · answered Apr 11 '13 at 02:53

0

SELECT 
   product_id
 , STUFF((
    SELECT '|' + CONVERT(VARCHAR(10), A.model_size)
    FROM [BD New Product Data] A 
    WHERE A.record_type = 'model' AND A.product_id = T.product_id
    FOR XML PATH(''), TYPE
   ).value('.', 'VARCHAR(MAX)'), 1, 1, '') AS product_sizes
 , STUFF((
    SELECT '|' + CONVERT(VARCHAR(10), A.model_quantity)
    FROM [BD New Product Data] A 
    WHERE A.record_type = 'model' AND A.product_id = T.product_id
    FOR XML PATH(''), TYPE
    ).value('.', 'VARCHAR(MAX)'), 1, 1, '') AS product_quantities
FROM [BD New Product Data] T 
WHERE T.record_type = 'product' 
 --AND T.product_id = '26206'

answered Apr 11 '13 at 02:53

bob

452
4
11

This is great! Thx. So how would I take this and make it an update statement to update the qty and size fields in the product record? – user2268505 Apr 11 '13 at 17:33
Sorry for the lack of explanation, I was pressed for time. Who knows why T-SQL doesn't offer a WM_CONCAT-like aggregate function, but it doesn't. Happily, it turns out `FOR XML PATH('')` can be used to accomplish the same thing. So the above uses XML to generate the concatenated string and then just uses STUFF() to remove the opening pipe, returning the final string. To update your qty and size fields, just change from `SELECT` to `UPDATE`, a la `UPDATE T SET product_size = STUFF(...), product_quantity = STUFF(...) FROM [BD New Product Data] T WHERE T.record_type = 'product'`. – bob Apr 12 '13 at 01:14
What's missing in the final Where? Update T Set T.size = STUFF(( SELECT '|' + CONVERT(VARCHAR(10), A.model_size) FROM [dbo].[BD New Product Data Test] A WHERE A.record_type = 'model' AND A.product_id = T.sku FOR XML PATH(''), TYPE ).value('.', 'VARCHAR(MAX)'), 1, 1, '') , T.qty = STUFF(( SELECT '|' + CONVERT(VARCHAR(10), A.model_quantity) FROM [dbo].[BD New Product Data Test] A WHERE A.record_type = 'model' AND A.product_id = T.sku FOR XML PATH(''), TYPE ).value('.', 'VARCHAR(MAX)'), 1, 1, '') FROM [dbo].[BD Active Test] T WHERE T.sku = A.product_id – user2268505 Apr 12 '13 at 03:27
To get it top update all product records in T, where t.sku = a.product_id – user2268505 Apr 12 '13 at 03:31

score 0 · Answer 2 · answered Apr 12 '13 at 03:55

USE Prod
Update dbo.[BD Active Test]
 Set dbo.[BD Active Test].size = STUFF((
    SELECT '|' + CONVERT(VARCHAR(10), [dbo].[BD New Product Data Test].model_size)
    FROM [dbo].[BD New Product Data Test] 
    WHERE [dbo].[BD New Product Data Test].record_type = 'model' AND [dbo].[BD New Product Data Test].product_id = dbo.[BD Active Test].sku
    FOR XML PATH(''), TYPE
   ).value('.', 'VARCHAR(MAX)'), 1, 1, '')
 , dbo.[BD Active Test].qty = STUFF((
    SELECT '|' + CONVERT(VARCHAR(10), [dbo].[BD New Product Data Test].model_quantity)
    FROM [dbo].[BD New Product Data Test] 
    WHERE [dbo].[BD New Product Data Test].record_type = 'model' AND [dbo].[BD New Product Data Test].product_id = dbo.[BD Active Test].sku
    FOR XML PATH(''), TYPE
    ).value('.', 'VARCHAR(MAX)'), 1, 1, '')
from dbo.[BD Active Test]
INNER JOIN dbo.[BD New Product Data Test] ON dbo.[BD Active Test].sku = dbo.[BD New Product Data Test].product_id 
where dbo.[BD New Product Data Test].record_type = 'PRODUCT'

I'm confused, where was this `[BD Active Test]` table and the `sku` column in the original post? In terms of the solution to your problem, this answer appears to be identical to my answer. — bob, Apr 12 '13 at 15:12
I had left out the fact that I was updating another table. This worked out.. Thanks for your help. — user2268505, Apr 13 '13 at 03:57

The recursive coalesce challenge

2 Answers2