DECLARE @products table (prdid varchar(40));
Insert INTO @products values('A111'),('B111'),('A123'), ('Z456');

with rec_query as (
                    ---anchor member
                     '
                     '
                    where pid IN @products
                    UNION ALL
                    ---recursive members
                    '
                    '
                  )

select prdid, rec_query.*