SELECT Parent-Child with SORT

I have a simple, one level parent child relation table, with following columns:

ID_Asset| Parent_ID_Asset | ProductTitle

I need output grouped by Parent followed by children, and also sorted by Parent and Children Name. My attempts in the fiddle. The parent must be first.

See here for details: https://rextester.com/PPCHG20007

```
CREATE TABLE [dbo].Test NOT NULL
) ON [PRIMARY]
GO

INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (2, NULL, N'Live Maps Unity')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (3, NULL, N'mShare')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (4, NULL, N'Nessus Professional')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (5, NULL, N'Enterprise Server')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (6, NULL, N'PhantomPDF')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (7, NULL, N'Sharegate')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (8, NULL, N'ADONIS Server')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (9, NULL, N'Automated Intelligence AI Compliance Extender & AI Syncpoint')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (10, NULL, N'Agility BridgeChecker')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (11, NULL, N'Office Timeline')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (12, NULL, N'ThinkBuzan iMindMap 8 Ultimate')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (13, NULL, N'Total Management Suite')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (14, NULL, N'Webex Business Messaging and Advanced Meetings')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID

You can get it by using CASE WHEN in ORDER BY clause:

Note: I've added COALESCE(Parent_ID_Asset, '') just to get NULL Parent_ID in first place, you could replace it by CASE WHEN Parent_ID_Asset IS NULL THEN 0 ELSE 1 END

SELECT 
    ID_Asset,
    Parent_ID_Asset,
    ProductTitle
FROM 
    Test
ORDER BY
    CASE WHEN Parent_ID_Asset IS NULL THEN ID_Asset ELSE Parent_ID_Asset END,
    COALESCE(Parent_ID_Asset, ''),
    ProductTitle;
GO

| ID_Asset | Parent_ID_Asset | ProductTitle                                                 |
|----------|-----------------|--------------------------------------------------------------|
| 2        | NULL            | Live Maps Unity                                              |
| 3        | NULL            | mShare                                                       |
| 4        | NULL            | Nessus Professional                                          |
| 5        | NULL            | Enterprise Server                                            |
| 6        | NULL            | PhantomPDF                                                   |
| 7        | NULL            | Sharegate                                                    |
| 8        | NULL            | ADONIS Server                                                |
| 16       | 8               | ADONIS Designer                                              |
| 20       | 8               | ADONIS Portal Module “Control & Release” Package XS          |
| 9        | NULL            | Automated Intelligence AI Compliance Extender & AI Syncpoint |
| 10       | NULL            | Agility BridgeChecker                                        |
| 11       | NULL            | Office Timeline                                              |
| 12       | NULL            | ThinkBuzan iMindMap 8 Ultimate                               |
| 13       | NULL            | Total Management Suite                                       |
| 14       | NULL            | Webex Business Messaging and Advanced Meetings               |

Rextester here

UPDATE

It seems you need the result ordered somehow by ProductTitle, you can use this query: (Keep in mind you're adding an extra job that maybe could be done at presentation layer.)

SELECT 
    ID_Asset,
    Parent_ID_Asset,
    ProductTitle
FROM 
    Test
ORDER BY
    MAX(CASE WHEN Parent_ID_Asset IS NULL THEN ProductTitle ELSE '' END) 
        OVER (PARTITION BY COALESCE(Parent_ID_Asset, ID_Asset)),
    COALESCE(Parent_ID_Asset, ID_Asset),
    Parent_ID_Asset,
    ProductTitle;

ID_Asset | Parent_ID_Asset | ProductTitle                                                
-------: | --------------: | :-----------------------------------------------------------
       8 |            null | ADONIS Server                                               
      16 |               8 | ADONIS Designer                                             
      20 |               8 | ADONIS Portal Module “Control & Release” Package XS     
      10 |            null | Agility BridgeChecker                                       
       9 |            null | Automated Intelligence AI Compliance Extender & AI Syncpoint
       5 |            null | Enterprise Server                                           
       2 |            null | Live Maps Unity                                             
       3 |            null | mShare                                                      
       4 |            null | Nessus Professional                                         
      11 |            null | Office Timeline                                             
       6 |            null | PhantomPDF                                                  
       7 |            null | Sharegate                                                   
      12 |            null | ThinkBuzan iMindMap 8 Ultimate                              
      13 |            null | Total Management Suite                                      
      14 |            null | Webex Business Messaging and Advanced Meetings

db<>fiddle here

StackExchange Database Administrators Q#223483, answer score: 6