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](
[ID_Asset] [int] NOT NULL,
[Parent_ID_Asset] [int] NULL,
[ProductTitle] [nvarchar](150) 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_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')
GO
SELECT
*
FROM
Test
ORDER BY (
CASE WHEN Parent_ID_Asset is null then ID_Asset else Parent_ID_Asset end), ProductTitle
GO
The desired output is (the relevant part):
ID_Asset Parent_ID_Asset ProductTitle
-------- --------------- ---------------------------------------------------
8 NULL ADONIS Server
16 8 ADONIS Designer
20 8 ADONIS Portal Module “Control & Release” Package XS
Parent on top, followed by children in alphabetical order. The parent elements (with null in Parent_ID_Asset
) must be also ordered alphabetically.
Best Answer
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 byCASE WHEN Parent_ID_Asset IS NULL THEN 0 ELSE 1 END
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.)
db<>fiddle here