SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE [dbo].[Test](
	[ID_Asset] [int] NOT NULL,
	[Parent_ID_Asset] [int] NULL,
	[ProductTitle] [nvarchar](150) NOT NULL
) ON [PRIMARY]


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_Asset], [ProductTitle]) VALUES (16, 8, N'ADONIS Designer')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (20, 8, N'ADONIS Portal Module “Control & Release” Package XS')

15 rows affected

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


;WITH X AS
(
SELECT 
    ID_Asset,
    Parent_ID_Asset,
    ProductTitle,
    COALESCE((SELECT 
                 t2.ProductTitle
              FROM
                 Test t2
              WHERE
                 t2.ID_Asset = t1.Parent_ID_Asset), t1.ProductTitle) ParentTitle
FROM 
    Test t1
)
SELECT 
    ID_Asset,
    Parent_ID_Asset,
    ProductTitle
FROM
    X
ORDER BY
    ParentTitle,
    ID_Asset,
    Parent_ID_Asset


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

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