Clever Geek Handbook

SQL Server: Select Parent Child

I have SQL Server 2008 with ProductCategories table created like this:

Id | Name      | ParentId
71   PCs         NULL
32   MACs        NULL
 3   Keyboard    1
 9   Mouse       1
 5   Screen      1
11   Keyboard    2
 7   Mouse       2
 8   Screen      2

I would like to select from this table and get the result set like this:

Id | Name      | ParentId
71   PCs         NULL
 3   Keyboard    1
 9   Mouse       1
 5   Screen      1
32   MACs        NULL
11   Keyboard    2
 7   Mouse       2
 8   Screen      2

I've tried this, but this obviously gives me the ones that don't have a ParentId:

WITH Hierarchy
AS
(
    SELECT 
        T1.Id, T1.ParentId
    FROM
        ProductCategories T1
    WHERE 
        T1.parentid IS NULL OR 
        T1.parentid IN (SELECT id from ProductCategories WHERE parentid IS NULL)
    UNION ALL
    SELECT 
        T1.Id, T1.ParentId
    FROM
        ProductCategories T1
    INNER JOIN 
        Hierarchy TH ON TH.Id = T1.ParentId
)
select *
from Hierarchy 
order by parentid

Please help me if you can :)

- The guy who doesn't know SQL

+2


source to share


2 answers


try this:

Select Id, Name, ParentId
From ProductCategories
Order By Coalesce(ParentId, Id), 
   Coalesce(ParentId, 0), Name


Three offers Order By,

  • Coalesce (ParentId, Id): it groups the entries by the parent for both the parent itself and all children of that parent
  • Coalesce (ParentId, 0) This group is inside each set, so that one entry with a null parent (parent) is sorted at the top of the group
  • Name. This will sort the children in the group by name
+3


source


try it

SELECT id, name, parentId 
FROM categories
ORDER BY ISNULL(parentId,id), id


Btw, shouldn't the first two indexes in your table be 1 and 2, not 71 and 32?

0


source






More articles:


All Articles