Recursive Function for MS SQL

Question

I need to write a recursive function to get the references from table B and combine in a field in table A.

Table A:

ID   Name      Reference
1    Item A 
2    Item B 
3    Item C

Table B:

ID   Parent_ID   Reference
1    1           ABC
2    1           DEF
3    2           GHI

Expected result:

ID   Name      Reference
1    Item A     ABCDEF
2    Item B     GHI
3    Item C

possible duplicate of How to make a query with group_concat in sql server — JimmyB
– JimmyB, Commented May 11, 2015 at 15:17
My expected results are: the reference of Item A (the ID 1 in table A) "ABCDEF" and for Item B "GHI". The recursion will be to get the references, and it should be recursive because table B can get up to 50 times. — Iohann Oro
– Iohann Oro, Commented May 11, 2015 at 15:18
This isn't recursion. Recursion is when a parent of one item can be the child of another, but since you are joining two tables, you can only go one level deep. — Tab Alleman
– Tab Alleman, Commented May 11, 2015 at 15:30

Giorgi Nakeuri · Accepted Answer · 2015-05-11 15:35:36Z

1

I don't see any recursion here:

DECLARE @a TABLE
    (
      ID INT ,
      Name VARCHAR(10) ,
      Reference VARCHAR(100)
    )
DECLARE @b TABLE
    (
      ID INT ,
      ParentID INT ,
      Reference VARCHAR(3)
    )

INSERT  INTO @a
VALUES  ( 1, 'Item A', NULL ),
        ( 2, 'Item B', NULL ),
        ( 3, 'Item C', NULL )

INSERT  INTO @b
VALUES  ( 1, 1, 'ABC' ),
        ( 2, 1, 'DEF' ),
        ( 3, 2, 'GHI' )


UPDATE  a
SET     Reference = ca.data
FROM    @a a
        CROSS APPLY ( SELECT    
                   (            SELECT  b.Reference
                                FROM    @b b
                                WHERE   a.ID = b.ParentID
                                ORDER BY ID
                      FOR       XML PATH('') ,
                                    TYPE
                   ).value('.', 'varchar(max)') AS DATA
                    ) ca

SELECT  *
FROM    @a

Output:

ID  Name    Reference
1   Item A  ABCDEF
2   Item B  GHI
3   Item C  NULL

answered May 11, 2015 at 15:35

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Iohann Oro Over a year ago

That's a great idea, I haven't thought about it. I'll try it and see if it works.

Iohann Oro Over a year ago

That's great! Thanks for the answer, this is a real better way to do it - instead of recursive function. Thank you again :)

Iohann Oro Over a year ago

Just one more question, how can I put a comma between the result? Just realized that it would be better to read if it was like this: "ABC, DEF" - I mean, I know that I just need to put it after the select, but there is any way to avoid it in the last one? So it would look "ABC, DEF" instead of "ABC, DEF,"

Giorgi Nakeuri Over a year ago

SELECT ',' + b.Reference FROM @b b

Collectives™ on Stack Overflow

Recursive Function for MS SQL

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related