hai to all, I have an product .that product have subproduct and also the subproduct may or maynot contain the product.i want to dispaly it in tree format.in oracle we can use the connect by prior. but i don't know in sql server for sample ITT1. SELECT I.father,I.code code from ITT1 as I father code 01 IC 0000200000 01 IC 0000201000 01 IC 0000200000 01 IC 0000201000 01 IC 0000200000 01 IC 0000201000 01 IC 0000200000 01 IC 0000201000 01 IC 0000201000 01 IC 0000201100 01 IC 0007801000 01 IC 0000201100 01 IC 0000201000 01 IC 0000201110 01 IC 0005501000 01 IC 0000201110 07 IC 0000401000 01 IC 0000201110 01 IC 0000201000 01 IC 0000201120 07 IC 0000401000 01 IC 0000201120 01 IC 0000201000 01 IC 0000201130 01 IC 0000201000 01 IC 0000201130 01 IC 0005501000 01 IC 0000201130 01 IC 0005501000 01 IC 0000201130 07 IC 0000401000 01 IC 0000201130 07 IC 0000401000 01 IC 0000201130 01 IC 0008400000 01 IC 0000601000 01 IC 0008400000 01 IC 0000601000 and more data is there . there is more level then how can i get in tree formated. can u help any one?
You may wants to use recursive CTE. Check this http://msdn2.microsoft.com/en-us/library/ms186243.aspx Madhu