CTE To Return Immediate Grand Parent

Hi All,

Can someone help me get the immediate grand parent information of a row?

1 Product 1 NULL NULL
2 Product 1 Child 1 1 NULL
3 Product 2 Child 1 2 NULL
5 Product 1 Child 1 Grand Child 1 2 1
6 Product 2 Child 1 Grand Child 1 2 3
4 Product 1 Child 2 1 NULL


Thanks,

Guran

create table Product
(
ProdId Int,
ProdDesc varchar(100),
ProdParentId Int
)

insert into product values (1, 'Product 1', NULL)
insert into product values (2, 'Product 1 Child 1', 1)
insert into product values (3, 'Product 2 Child 1', 2)
insert into product values (4, 'Product 1 Child 2', 1)
insert into product values (5, 'Product 1 Child 1 Grand Child 1', 2)
insert into product values (6, 'Product 1 Child 1 Grand Child 2', 2)