Recursive SQL Query

Question

First of all thanks for the help , I'm no expert on sql and maybe do a very basic question

my database is SQL Server 2012

I have a table with the fields shown below

servicerequestareaid - enumtypeid              -value             -ordinal -    parentid
69  - D115C073-613F-F8C4-04A1-DE63B28D2496 -    Servidor Exchange - 0          - 68
70  - 3A09DA42-B33E-05EA-C2CB-167FECBDE346 -    Rol CAS           - 0          - 69
71  - E9AFC8C0-76F3-2B92-38A3-7A5B0F9FCD07 -    Perimetral        - 0          - 70
72  - 925F6D8C-EA24-798F-3D6C-EB64AC436D6F -    Asegurado         - 0          - 71

I need to take the value of servicerequestareaId , enumtypeId and parentId field for that record

i begin with a value of servicerequestareaid,

parentid value corresponds to the value stored in the servicerequestareaid field to another record

this process is repeated until the value stored in parentId is equal to 1

after having that set of values stored must display

create two cursors for this purpose but do not want to do it that way , should habrer a more efficient way to do

![Cursor][2]

thanks for the help

You want to just stitch the whole table together? Or you are given a specifc servicerequestareaId to begin with? — Bill Gregg
– Bill Gregg, Commented Feb 23, 2015 at 13:06
thx billI have a servicerequestareaid to Begin is a variable thx rhys my database is SQL Server 2012 — Heretic
– Heretic, Commented Feb 23, 2015 at 13:09

Giorgi Nakeuri · Accepted Answer · 2015-02-23 13:30:46Z

1

Do it in recursive cte:

DECLARE @t TABLE(id INT, parent_id int)

INSERT INTO @t VALUES
(1, null),
(2, null),
(3, 1),
(4, 3),
(5, 2),
(6, 2),
(7, 5)


DECLARE @id INT = 7

;WITH cte AS(
SELECT * FROM @t WHERE id = @id
UNION ALL
SELECT t.* FROM @t t JOIN cte ON t.id = cte.parent_id
)
SELECT * FROM cte

Output:

id  parent_id
7   5
5   2
2   NULL

I think you will easily adjust this to your tables.

answered Feb 23, 2015 at 13:30

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

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

Collectives™ on Stack Overflow

Recursive SQL Query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related