VBA code for CTE Recursive in SQL Data table

[Dineshkumar Palanisamy K]

Hi,

I have Data table which has data as shown below

In this I need to get the "Component" data which are structured under "Item" and after that each component will be considered as "Item" and recursive CTE comes here. Please help me to sort this out.

Sample Input:

Sample Output

in this Item is Assy. no and Component data need to be populated in Respective levels.

For example

"001-1194-8" will be given as Input, so it is considered as "Item" and 1st level data will be "Component" from data table. After that this "component" (001-1194-8FL) will be considered as "Item" and their respective "component" will be populated in adjacent cell and it has to be done till now item is found in "Item" data as shown in sample Output.

Thanks in advance.

 

[shknbk2]

I got something working strictly based on the info you provided. Not knowing where your data exists, I assumed that the input table started at A1, therefore, my code starts with A2 and works down. Also, I don't know where your output table is. I put H2 as a testing location, but you will have to change it to the right location.

There is no making sure the output table is blank or anything like that. I don't know what your data looks like when you want to start the Sub.

Sub GenerateOutput()
    Dim sNo As Integer
    Dim output1(1 To 1, 1 To 5) As String
    Dim output2(1 To 1, 1 To 5) As String
    Dim output3(1 To 1, 1 To 5) As String
    Dim rSource As Range
    Dim rTarget As Range
    
    sNo = 0
    Set rSource = Range("A2")
    Set rTarget = Range("H2")
    
    Do While rSource.Value <> ""
        ClearOutputs output1, output2, output3
        sNo = sNo + 1
        output1(1, 1) = sNo
        output1(1, 2) = rSource.Offset(0, 2).Value
        output2(1, 1) = rSource.Offset(0, 4).Value
        output2(1, 3) = rSource.Offset(0, 5).Value
        output2(1, 5) = rSource.Offset(0, 3).Value
        rTarget.Resize(1, 5).Value = output1
        Set rTarget = rTarget.Offset(1, 0)
        rTarget.Resize(1, 5).Value = output2
        Set rSource = rSource.Offset(1, 0)
        Set rTarget = rTarget.Offset(1, 0)
        If rSource.Offset(0, 2).Value = output2(1, 3) Then
            output3(1, 1) = rSource.Offset(0, 4).Value
            output3(1, 4) = rSource.Offset(0, 5).Value
            output3(1, 5) = rSource.Offset(0, 3).Value
            rTarget.Resize(1, 5).Value = output3
            Set rTarget = rTarget.Offset(1, 0)
            Set rSource = rSource.Offset(1, 0)
        End If
    Loop
End Sub

Sub ClearOutputs(ByRef o1() As String, ByRef o2() As String, ByRef o3() As String)
    Dim i As Integer
    
    For i = 1 To 5
        o1(1, i) = ""
        o2(1, i) = ""
        o3(1, i) = ""
    Next
End Sub

I was able to produce the following:

CTE recursive.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Company	Clent	Item	qtyper	mtlseq	Component
2	AA	100	001-1194-8	1	0030	001-1194-8FL		1	001-1194-8
3	AA	100	001-1194-8FL	173.434	0010	NS10160121		0030		001-1194-8FL		1
4	AA	100	001-1210-2	1	0030	001-1210-2FL		0010			NS10160121	173.434
5	AA	100	001-1210-2FL	3.875	0010	NS10160121		2	001-1210-2
6	AA	100	001-1228-4	0.65	0010	NS06148121		0030		001-1210-2FL		1
7	AA	100	001-1229-2	1	0030	001-1229-2FL		0010			NS10160121	3.875
8	AA	100	001-1229-2FL	0.935	0010	NS06148121		3	001-1228-4
9								0010		NS06148121		0.65
10								4	001-1229-2
11								0030		001-1229-2FL		1
12								0010			NS06148121	0.935
Sheet1

 

[Dineshkumar Palanisamy K]

I got something working strictly based on the info you provided. Not knowing where your data exists, I assumed that the input table started at A1, therefore, my code starts with A2 and works down. Also, I don't know where your output table is. I put H2 as a testing location, but you will have to change it to the right location.

There is no making sure the output table is blank or anything like that. I don't know what your data looks like when you want to start the Sub.

Sub GenerateOutput()
    Dim sNo As Integer
    Dim output1(1 To 1, 1 To 5) As String
    Dim output2(1 To 1, 1 To 5) As String
    Dim output3(1 To 1, 1 To 5) As String
    Dim rSource As Range
    Dim rTarget As Range
   
    sNo = 0
    Set rSource = Range("A2")
    Set rTarget = Range("H2")
   
    Do While rSource.Value <> ""
        ClearOutputs output1, output2, output3
        sNo = sNo + 1
        output1(1, 1) = sNo
        output1(1, 2) = rSource.Offset(0, 2).Value
        output2(1, 1) = rSource.Offset(0, 4).Value
        output2(1, 3) = rSource.Offset(0, 5).Value
        output2(1, 5) = rSource.Offset(0, 3).Value
        rTarget.Resize(1, 5).Value = output1
        Set rTarget = rTarget.Offset(1, 0)
        rTarget.Resize(1, 5).Value = output2
        Set rSource = rSource.Offset(1, 0)
        Set rTarget = rTarget.Offset(1, 0)
        If rSource.Offset(0, 2).Value = output2(1, 3) Then
            output3(1, 1) = rSource.Offset(0, 4).Value
            output3(1, 4) = rSource.Offset(0, 5).Value
            output3(1, 5) = rSource.Offset(0, 3).Value
            rTarget.Resize(1, 5).Value = output3
            Set rTarget = rTarget.Offset(1, 0)
            Set rSource = rSource.Offset(1, 0)
        End If
    Loop
End Sub

Sub ClearOutputs(ByRef o1() As String, ByRef o2() As String, ByRef o3() As String)
    Dim i As Integer
   
    For i = 1 To 5
        o1(1, i) = ""
        o2(1, i) = ""
        o3(1, i) = ""
    Next
End Sub

I was able to produce the following:

CTE recursive.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Company	Clent	Item	qtyper	mtlseq	Component
2	AA	100	001-1194-8	1	0030	001-1194-8FL		1	001-1194-8
3	AA	100	001-1194-8FL	173.434	0010	NS10160121		0030		001-1194-8FL		1
4	AA	100	001-1210-2	1	0030	001-1210-2FL		0010			NS10160121	173.434
5	AA	100	001-1210-2FL	3.875	0010	NS10160121		2	001-1210-2
6	AA	100	001-1228-4	0.65	0010	NS06148121		0030		001-1210-2FL		1
7	AA	100	001-1229-2	1	0030	001-1229-2FL		0010			NS10160121	3.875
8	AA	100	001-1229-2FL	0.935	0010	NS06148121		3	001-1228-4
9								0010		NS06148121		0.65
10								4	001-1229-2
11								0030		001-1229-2FL		1
12								0010			NS06148121	0.935
Sheet1

Hi shknbk2,

Thanks for your time.

Actually, the data that we receive from SQL Database is the Input. And code has to run through the data and have to produce the output as shown.