Recursive query in SQL Server With All Childs

Question

i have searched on the forum for a problem like that i face as i didn't found one , i start a new topic

I have a table named SubProduct with the following structure:

ProductCode    | SubProduct    | Unit    
11470060       | 11470060.DSP  |  12    
11470060.DSP   | 11470060.EA   |  12    
12110957       | 12110957.DSP  |  12    
12110957.DSP   | 12110957.EA   |  50

I need to get to this result, but I can't reach it:

Parent             Child            Unit    
11470060       | 11470060.DSP  |  12    
11470060       | 11470060.EA   |  144 (12 * 12)     
12110957       | 12110957.DSP  |  12    
12110957       | 12110957.EA   |  600 (12 * 50)

Thanks for the help :)

Welcome to Stack Overflow! Please provide additional information to help with your specific problem. What have you tried that is not working? This is not a code-writing service. https://stackoverflow.com/help/mcve — squillman, Jun 19 '18 at 14:14

Gordon Linoff · Accepted Answer · 2018-06-20T01:31:40.903

0

You need to identify the parent, which you can do using string operations. Then you can use a join for the multiplication.

The following returns what you want in a slightly different form:

with sp as (
      select sp.*,
             left(productcode, charindex('.', productcode + '.') - 1) as parent
      from subproduct sp
     )
select spd.parent, spd.unit * spe.unit,
       spd.subproduct as dsp_subproduct, spe.subproduct as ea_subproduct
from sp spd join
     sp spe
     on spd.parent = spe.parent and
        spd.subproduct like '%.DSP' and
        spe.subproduct like '%.EA';

You can get this on separate rows using apply:

with sp as (
      select sp.*,
             left(productcode, charindex('.', productcode + '.') - 1) as parent
      from subproduct sp
     ) 
select spd.parent, v.subproduct, v.unit
from sp spd join
     sp spe
     on spd.parent = spe.parent and
        spd.subproduct like '%.DSP' and
        spe.subproduct like '%.EA' outer apply
     (values (spd.subproduct, spd.unit), (spe.subproduct, spd.unit * spe.unit) ) v(subproduct, unit);

Here is a rextester demonstrating the solution.

edited Jun 20 '18 at 01:31

answered Jun 19 '18 at 14:33

Gordon Linoff

1,242,037
58
646
786

thank you for your response it's very helpful i took the second part using apply otherwise it's not shown the requested values the request returns result like this 11470060 11470060.DSP 144 11470060 11470060.EA 144 otherwise the result should be 11470060 11470060.DSP 12 11470060 11470060.EA 144 also not all the product_code have 2 child DSP And EA there are one's with only one child EA and they are not returned i dont know if i explained it clearly – Marouane Jaib Jun 19 '18 at 15:04
@MarouaneJaib . . . That is easily fixed. I added a rextester showing the solution. If you have a problem with other data (such as missing children of one type or the other), then ask *another* question with appropriate sample data and explanation. This answer clearly answers the question that you asked here (and if you approve of the answer, you should accept it). – Gordon Linoff Jun 20 '18 at 01:32
Thank's a lot for your help – Marouane Jaib Jun 20 '18 at 08:25

Recursive query in SQL Server With All Childs

1 Answers1