sql server get multiple levels of data

Question

How can I achieve the following using SQL server? I am trying to get page counts in Sitefinity.

The table structure has lot of columns but below are the ones that matter

**Id**                  **parentid**                **title**
 ABC                        DEF                     title1
 XYZ                        DEF                     title2
 LLL                        DEF                     title3
 PPP                        ABC                     title4
 III                        ABC                     title5
 WWW                        PPP                     title6

A page can have subpages, and a subpage can have further subpages. In database, a subpage is represented with ID column and the parent page with parentID. How can i get count of children for each parent?

I don't have the answer right off, but you might look into CTEs, I am pretty sure that will help you build a recursion in a stored proc... Just a thought. — aserwin, Sep 26 '12 at 20:05

score 0 · Answer 1 · answered Sep 26 '12 at 20:17

If I understand correctly, something like this should get you started:

select id as parent,
       title as title,
       count(*) as number_children
  from table as parent
 inner join table as children
    on children.parentid = parent.id
 group by 1,2

score 0 · Accepted Answer · edited May 23 '17 at 11:48

I have got the answer from the other thread : Hierarchical Queries in SQL Server 2005.

  WITH Parent AS
(
    SELECT
        ID,
        ParentID,
        Name AS Path
    FROM
        tblHierarchy
    WHERE
        ParentID IS NULL

    UNION ALL

    SELECT
        TH.ID,
        TH.ParentID,
        CONVERT(varchar(128), Parent.Path + '/' + TH.Name) AS Path
    FROM
        tblHierarchy TH
    INNER JOIN
        Parent
    ON
        Parent.ID = TH.ParentID
)
SELECT * FROM Parent

sql server get multiple levels of data

2 Answers2