Thu 8 Nov 2012

If you want to show records (Parent Child concatenation), then following SQL Server recursive query could help you in achieve. For a clear picture have a look at following snap

 

 Recursive+Qeury+Parent+Child+Concatenation.jpg

Recursive Query showing only child Records

                           

with parentChildResult as

(

       select ItemId,

                     ItemDescription,

                     ItemCategoryId,

                     cast('' as nvarchar(max)) as ParentNames

       from Items

       where ItemCategoryId is null

        union all

        select i2.ItemId,

                     i2.ItemDescription,

                     i2.ItemCategoryId,

                     parentChildResult.ParentNames + ' > ' + parentChildResult.ItemDescription

       from Items as i2

       inner join parentChildResult

              on parentChildResult.ItemId = i2.ItemCategoryId

)     

 select ItemId,

              stuff(ParentNames, 1, 3, '') + ' > ' + ItemDescription as ParentNames

from parentChildResult

 where ItemId in

(

       SELECT i.ItemId FROM Items i

       WHERE NOT EXISTS(SELECT 1 FROM Items I2 WHERE i.ItemId = I2.ItemCategoryId)

)

 

order by ParentNames

 

  

Recursive Query showing All parents and Child Records

 

with parentChildResult as

(

       select ItemId,

                     ItemDescription,

                     ItemCategoryId,

                     cast('' as nvarchar(max)) as ParentNames

       from Items

       where ItemCategoryId is null

        union all

        select i2.ItemId,

                     i2.ItemDescription,

                     i2.ItemCategoryId,

                     parentChildResult.ParentNames + ' > ' + parentChildResult.ItemDescription

       from Items as i2

       inner join parentChildResult

              on parentChildResult.ItemId = i2.ItemCategoryId

)     

 select ItemId,

              stuff(ParentNames, 1, 3, '') + ' > ' + ItemDescription as ParentNames

from parentChildResult

 order by ParentNames

 

Comments here


Add Comment Post comment

 
 
 
   Country flag

Loading