0

I have a view in my SQL Server database which basically holds hierarchical information between some records based on id values of type int. A simple representation is as follows:

ID    Parent_ID   
1     NULL  
2     1  
3     2  
4     NULL  
5     4

By using this view I am trying to generate another view. I want all records that derive from ID=1 (which are 1, 2 and 3) to be crossed with a set, while other records (4, 5) be crossed another set. As an example crossing all records that derive from ID=1 with set (1,2) and crossing other records with set 3, I want a view as follows:

ID    Value  
1      1  
1      2  
2      1  
2      2  
3      1  
3      2  
4      3  
5      3
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
quantum
  • 3
  • 1
  • What kind of problems are you having? What have you tried? – BWS Dec 10 '13 at 14:17
  • Crossing a table with a set is possible, but I need a mechanism to have all ancestors of an object to check if the id of an object is inside to decide with which set i need to cross with. It seems that i need a cursor-like method. – quantum Dec 10 '13 at 16:38
  • I think the magic lies beneath CTE, but i am not sure how to use – quantum Dec 10 '13 at 18:05

0 Answers0