3

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.

  • Not the same, I'm dealing with single level only. – Petr Šimůnek Nov 26 '18 at 22:08
  • Welcome to DBA.SE. We appreciate your participation.Currently your question doesn't contain enough information and might be closed as unclear what you're asking. Could you please add an example of what the output should look like? Your question and comment contain contradicting information: single level vs. two levels (child : parent relationship). It's hard to determine what you want. Edit your question and add some example output. – hot2use Nov 27 '18 at 6:45
  • Updated accordingly, thanx. – Petr Šimůnek Nov 27 '18 at 8:52
6

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

  • That's it ! Works great and I have to study it :O) Thank you ! PS: I'm working with K2 frontend which unfortunately does not allow for such sort. – Petr Šimůnek Nov 27 '18 at 11:27
  • I'm glad to help. – McNets Dec 19 '18 at 16:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.