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](http://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server) — JimmyB, May 11 '15 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, May 11 '15 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, May 11 '15 at 15:30
It's not duplicated from the other question, because in that case he just wanted to make a select and see the results, in my case I need to update the field in database. I believe that the better way to do it is using recursion, am I wrong? — Iohann Oro, May 11 '15 at 15:32

score 1 · Accepted Answer · answered May 11 '15 at 15:35

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 '15 at 15:35

Giorgi Nakeuri

35,155
8
47
75

That's a great idea, I haven't thought about it. I'll try it and see if it works. – Iohann Oro May 11 '15 at 15:42
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 May 11 '15 at 15:56
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," – Iohann Oro May 11 '15 at 16:07
SELECT ',' + b.Reference FROM @b b – Giorgi Nakeuri May 11 '15 at 16:11

Recursive Function for MS SQL

1 Answers1