Order by query optimization for multi select data (enum) We previously used to store multi select data (enum) using pipe separated characters, as shown below like this - which made ordering on that enum data very simple.

╔═══╦════════════╦═════════════╗
║   ║ ID         ║ Alphabets   ║
╠═══╬════════════╬═════════════╣
║ 1 ║ 1          ║ |A|B|C|     ║
║ 2 ║ 2          ║ |B|D|       ║
║ 3 ║ 3          ║ |A|C|D|     ║
║ 4 ║ 4          ║ |A|C|       ║
║ 5 ║ 5          ║ |A|B|D|     ║
╚═══╩════════════╩═════════════╝


This above structure was easier to sort on the stored "Alphabets" values but search was expensive since we had to search using %|A|%.

Now we are using the following structure to store the same above data since searching the data becomes much easier:

╔═══╦════════════╦═════════════╗
║   ║ ID         ║ Alphabets   ║
╠═══╬════════════╬═════════════╣
║ 1 ║ 1          ║ A           ║
║ 2 ║ 1          ║ B           ║
║ 3 ║ 1          ║ C           ║
║ 4 ║ 2          ║ B           ║
║ 5 ║ 2          ║ D           ║
║ 6 ║ 3          ║ A           ║
║ 7 ║ 3          ║ C           ║
║ 8 ║ 3          ║ D           ║
║ 9 ║ 4          ║ A           ║
║ 10║ 4          ║ C           ║
║ 11║ 5          ║ A           ║
║ 12║ 5          ║ B           ║
║ 13║ 5          ║ D           ║
╚═══╩════════════╩═════════════╝


Searching becomes easy now but when I try to sort the data it gives nightmares.
Can anyone give me a good idea to sort this above new data-structure using sql server so that I get the same sorted results as with the previous data-structure??
Below is an example of how you can return the ordered list using your new structure.  What is missing from the database design is an ID table with one row per ID.  This would then provide a one-to-many relationship in 3NF, and avoid the need to select the distinct ID values from the many side.  You could use that parent table instead of the derived table in this example, which would be more efficient.

CREATE TABLE dbo.Alphabets(
    ID int NOT NULL
    ,Alphabets char(1) NOT NULL
    ,CONSTRAINT PK_Alphabets PRIMARY KEY (ID, Alphabets)
    );

INSERT INTO dbo.Alphabets VALUES
     (1, 'A')
    ,(1, 'B')
    ,(1, 'C')
    ,(2, 'B')
    ,(2, 'D')
    ,(3, 'A')
    ,(3, 'C')
    ,(3, 'D')
    ,(4, 'A')
    ,(4, 'C')
    ,(5, 'A')
    ,(5, 'B')
    ,(5, 'D');

SELECT  ID
        , ( SELECT    '|' + Alphabets
            FROM      dbo.Alphabets AS b
            WHERE     b.ID = a.ID
            ORDER BY  b.Alphabets
        FOR
            XML PATH('')
        ) + '|' AS Alphabets
FROM    ( SELECT DISTINCT
                    ID
            FROM      dbo.Alphabets
        ) AS a
ORDER BY Alphabets DESC;