SQL grouping by parent child

If I had this structure with the columns:

Primary_Key, Name, Parent_Primary_ID, DISPLAY_ORDER

1 Event NULL 1开发者_运维问答

2 News NULL 2

3 Event_List 1 1

4 Event_Detail 1 2

5 News_List 2 1

6 News_Details 2 2

how would you return data like:

1 Event

3 Event_List

4 Event_Detail

2 News

5 News_List

6 News_Detail

Thanks Rob

If SQL Server 2005+

DECLARE @YourTable TABLE 
(Primary_Key INT PRIMARY KEY,
 Name VARCHAR(100), 
 Parent_Primary_ID INT NULL,
 DISPLAY_ORDER INT)


INSERT INTO @YourTable
SELECT 1,'Event',NULL,1 UNION ALL
SELECT 2,'News',NULL,2 UNION ALL
SELECT 3,'Event_List',1,1 UNION ALL
SELECT 4,'Event_Detail',1,2 UNION ALL
SELECT 5,'News_List',2,1 UNION ALL
SELECT 6,'News_Details',2,2;

WITH Hierarchy
     AS (SELECT *,
                path = CAST(DISPLAY_ORDER AS VARCHAR(100))
         FROM   @YourTable
         WHERE  Parent_Primary_ID IS NULL
         UNION ALL
         SELECT y.Primary_Key,
                y.Name,
                y.Parent_Primary_ID,
                y.DISPLAY_ORDER,
                CAST(path + '.' + CAST(y.DISPLAY_ORDER AS VARCHAR) AS VARCHAR(100))
         FROM   @YourTable y
                JOIN Hierarchy h
                  ON h.Primary_Key = y.Parent_Primary_ID)
SELECT Primary_Key,
       Name
FROM   Hierarchy
ORDER  BY path  

Try (asumming standardish sql is supported)

DECLARE @YourTable TABLE 
(Primary_Key INT PRIMARY KEY,
 Name VARCHAR(100), 
 Parent_Primary_ID INT NULL,
 DISPLAY_ORDER INT)


INSERT INTO @YourTable
SELECT 1,'Event',NULL,1 UNION ALL
SELECT 2,'News',NULL,2 UNION ALL
SELECT 3,'Event_List',1,1 UNION ALL
SELECT 4,'Event_Detail',1,2 UNION ALL
SELECT 5,'News_List',2,1 UNION ALL
SELECT 6,'News_Details',2,2;

select
    primary_key = t1.primary_key,
    name = t1.name
from
    @YourTable t1
    left join @YourTable t2 on t1.parent_primary_id = t2.Primary_Key
order by
    coalesce(t2.DISPLAY_ORDER,t1.DISPLAY_ORDER,0),
    case    
        when t2.Primary_Key is null then 0
        else t1.DISPLAY_ORDER
    end

I don't see any grouping in your results. Unless you are trying to do something you aren't telling us I would use the query below:

SELECT Primary_Key, Name FROM YourTable

I don't see how you are ordering those results so I didn't try to order them.